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.
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.
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;
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.