BookmarkSubscribeRSS Feed
belboy
Obsidian | Level 7

HI,

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.

 

For example,

SAS shows a 10 variable dataset with 0 observations:

RSID  FULLNAME  Priv  Date  EMAIL  Ugkdd10  Jason Thomas  DBA  2008  jt@hotmail.com

 

I need SAS to show me a 5 variable dataset with 1 observation:

RSID,           FULLNAME,         Priv,     Date,       EMAIL

Ugkdd10      Jason Thomas      DBA     2008        jt@hotmail.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.

 

 

11 REPLIES 11
Tom
Super User Tom
Super User

Don't ask the same question multiple times.

belboy
Obsidian | Level 7

OK - thought the first post was in the wrong discussion board.

Cynthia_sas
SAS Super FREQ
Hi:
In order to answer your question, we need to see what the CSV file actually looks like. If you open the CSV file with notepad, do you see 1 line/row in the file or 2 lines? I am guessing that there might not be a carriage control character or line feed character at the end of the header line. Instead of PROC IMPORT, you might try a DATA step program because then you could specify the delimiter and the "TERMSTR" character if, for some reason, the character used as the linefeed is not '0D0A'x.

Please post the actual CSV file you are trying to read otherwise it's premature to consider using PROC TRANSPOSE or anything else.

cynthia
cynthia
belboy
Obsidian | Level 7

Thanks,

Here is one of the CSV files... I have several just like this.  It is very short and for some reason SAS imports every last one as 5+ headers and 0 obs.

 

 

Tom
Super User Tom
Super User

That sample file should work fine as it is using CRLF as end of line markers.

belboy
Obsidian | Level 7

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.  

 

Tom
Super User Tom
Super User

Just write your own data step to read the file. For five variables it is probably less typing that writing the proc import code.

Tom
Super User Tom
Super User

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;

 

Cynthia_sas
SAS Super FREQ

Hi:

  Thanks for posting. It reads fine with a DATA step INFILE/INPUT. This worked for me.

Cynthia

read_csv.png

belboy
Obsidian | Level 7

THANKS ALL,

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 🙂

 

data do_&appname;
set &folder;
RACFID = scan(f1,1,",");
'Full name'n = scan(f1,2,",");
Server = scan(f1,4,",");
KFA = scan(f1,5,",");
x=countw(kfa,"|");
do i = 1 to x;
x1=scan(kfa,i,"|");
Privilege = catx("-",scan(f1,3,","),scan(f1,4,","),x1);
drop i x;
output;
end;

run;

Cynthia_sas
SAS Super FREQ
Hi:
Since you are dealing with RACF and RACF IDs, I wonder whether you have some other type of issue with CRLF on the mainframe. I used your dataset, unedited, to generate the results shown in my screen shot. I'm glad you got it working, but you should not have to parse the way you did, if you can get the CRLF to work for the way the file was created.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1422 views
  • 0 likes
  • 3 in conversation