03-10-2018 01:21 PM
I'm using the import wizard with SAS to import a MS Excel CSV file. The problem is that SAS imports the entire file as a header with no observations.
SAS shows a 10 variable dataset with 0 observations:
RSID FULLNAME Priv Date EMAIL Ugkdd10 Jason Thomas DBA 2008 firstname.lastname@example.org
I need SAS to show me a 5 variable dataset with 1 observation:
RSID, FULLNAME, Priv, Date, EMAIL
Ugkdd10 Jason Thomas DBA 2008 email@example.com
I couldn't find any documentation of how to do this. Maybe proc transpose with some other formula?
Do I need to modify the import statement or figure out a way to recreate the dataset with SAS code (i.e., SCAN, SUBSTR, etc)?
Any help is very much appreciated.
03-10-2018 02:33 PM
03-10-2018 02:40 PM
03-10-2018 02:52 PM
That's what I was hoping for...but unfortunately, SAS imports everything into the header row.
Although TERMSTR=CRLF , I still get everything into a header.
Is there a way I can use SUBSTR to extract certain variables/header into observations instead of headers?
Thanks for any help.
03-10-2018 02:50 PM - edited 03-10-2018 02:52 PM
Run something like this to see what is actually in the file.
This program will read 5 blocks of 100 bytes and show you what is in there. You can check if there are CR, '0D'x, or LF, '0A'x, or both between the lines. Also whether your delimiter is a comma or a tab, '09'x.
data _null_; infile 'tester.csv' recfm=f lrecl=100 obs=5 ; input; list; run;
03-13-2018 10:01 AM
Unfortunately these solutions didn't work for me, the solution that did work is posted below. @Cynthia_sas your solution allowed my variables to come in correctly, but I wasn't able to get the observations. Nevertheless, I wrote my own data step that is working.
there may be a more efficient way of coding - but this works just fine so far
RACFID = scan(f1,1,",");
'Full name'n = scan(f1,2,",");
Server = scan(f1,4,",");
KFA = scan(f1,5,",");
do i = 1 to x;
Privilege = catx("-",scan(f1,3,","),scan(f1,4,","),x1);
drop i x;
03-13-2018 10:07 AM