From PHPMyAdmin to SAS Studio for lazy people

I need to get a data set into SAS for a course I’m teaching in March. Students like real data and some kind folks were willing to allow their de-identified data to be used. Win-win.

How did I get this data? In a SAS data set with a handy code book? Oh, very funny!

I received a login to PHPMyAdmin where the data which were definitely not created for my personal convenience reside.

First, I downloaded the data as CSV for Excel. This gave me a file where everything was like this.

re_apply;”consumer_id”;”email”;”counselor”;”gender”;”date_of_birth”;”age”;”primary_disability”;”secondary_disability”;”education”;”member”;”tribe”;”district”;”job_when_entered”;”if_job_earnings”;”earnings_type_before”;”referral”;”other_refer”;”application_date”;”assessment_date”;”eligibility_date”;”ipe_date”;”notify_rights”;”vocational_goal”;”state_vr”;”status”;”closure_date”;”status_type”;”employment_date”;”type_employ”;”start_job_earnings”;”post_job_earnings”;”earnings_type_after”;”intermediate_goal”;”semesters”;”int_completed”;”intermediate_date”;”last_contact_date”;”comment”

Yes, one, long line with everything in quotes and every column separated by a semi-colon. These are the column names but all of the data are in this exact same format as well.

Of course, you COULD upload this file and read it into SAS but that would take time and effort.

OR you can download a regular CSV or ODS spreadsheet file, just pick one of the other options, and then all your data would be in nice columns but you have no header row. Of course, that’s pretty easy to write an input statement in SAS. You just need to type in a hundred or variable names and be sure to have the format correctly specified. Not hard but probably take you more than a minute.

You COULD theoretically download the data as an SQL file and use SQL connect according to some smart and less lazy people on SAS Communities.

And, as some of the posters noted on that forum, not everyone has access to SQL connect.

Or, you could be a completely lazy person like me and fix it all in about 12 clicks.

Here is how:

1. Download the file as CSV for Excel
2. Copy and paste the first line, the header, into Word
3. Replace all the quotes with nothing, using Replace from the EDIT menu
4. Under the TABLE command select “CONVERT TEXT TO TABLE”. For “Separate text at”, click on Other and put in a semi-colon.
5. Now you have all of your filenames in nice columns as a table, copy that.
6. Download the file again as CSV
7. Insert a row and paste the filenames you copied in Step 5
8. Save that file as an Excel file
9. Upload it into SAS Studio
10. Under TASKS and UTILITIES, select IMPORT DATA, drag your file you uploaded to the window and click on the little running guy.