10-06-2017 03:48 PM
I am merging several spreadsheets in SAS that I obtained from Qualtrics. For some reason, when read into SAS the same question in one spreadsheet reads in as numeric, but character in another spreadsheet. Is there a way, using proc import, to make sure all the variables from the 7 spreadsheets (downloaded from Qualtrics) are read in as character variables? It gets annoying having to convert to figure out which variables are being read in as numeric from which spreadsheet and then having to convert it.
10-06-2017 03:59 PM
Short answer: no. Proc import makes guesses about the structure, depending on the values it finds, and therefore has different outcomes.
If you have a text-based file (not native Excel, but csv or similar), you can copy a data step (that was created by proc import) from the log and use that.
10-06-2017 04:09 PM
When you use proc import to read a csv file, it writes the data step (that actually does the import) to the log. Copy that, adapt it (eg for maximum expectable string lengths), and use it on all files to get consistent structure.
10-06-2017 04:30 PM
10-06-2017 04:46 PM
Spreadsheets can only be read in via PROC IMPORT. You cannot easily specify the variable type or format because Excel doesn't force any structure on the data.
If you want to be able to specify the structure, you copy the code from the log, holding down ALT key while you're selecting to remove the row numbers. The full code, not the snippet you've shown, includes the path to the file.
Take the code, change the path to the file and the data set name and use that to import your new data set.
10-06-2017 05:23 PM
PROC IMPORT writes really ugly code.
To generate a data step from a CSV file follow these steps.
You should now have a full data step for reading your file structure. For example here is a data step to read a csv file made from the SASHELP.CLASS dataset.
data want ; infile 'class.csv' dsd truncover firstobs=2; length Name $20 Sex $1 Age Height Weight 8; input name -- weight; run;
To re-use the program to read multiple files with the same structure just change the output dataset name and the input text file name.