topic Re: SAS import entire file as a header - how do I resolve? in SAS Procedures

SAS import entire file as a header - how do I resolve?

belboy — Sat, 10 Mar 2018 18:21:40 GMT

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.

Re: SAS import entire file as a header - how do I resolve?

Tom — Sat, 10 Mar 2018 19:33:29 GMT

Don't ask the same question multiple times.

Re: SAS import entire file as a header - how do I resolve?

Cynthia_sas — Sat, 10 Mar 2018 19:33:57 GMT

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

Re: SAS import entire file as a header - how do I resolve?

belboy — Sat, 10 Mar 2018 19:40:30 GMT

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.

Re: SAS import entire file as a header - how do I resolve?

belboy — Sat, 10 Mar 2018 19:42:04 GMT

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

Re: SAS import entire file as a header - how do I resolve?

Tom — Sat, 10 Mar 2018 19:45:48 GMT

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

Re: SAS import entire file as a header - how do I resolve?

Tom — Sat, 10 Mar 2018 19:52:09 GMT

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;

Re: SAS import entire file as a header - how do I resolve?

belboy — Sat, 10 Mar 2018 19:52:38 GMT

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.

Re: SAS import entire file as a header - how do I resolve?

Tom — Sat, 10 Mar 2018 19:53:49 GMT

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

Re: SAS import entire file as a header - how do I resolve?

Cynthia_sas — Sat, 10 Mar 2018 21:57:25 GMT

Hi:

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

Cynthia

Re: SAS import entire file as a header - how do I resolve?

belboy — Tue, 13 Mar 2018 14:01:31 GMT

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;

Re: SAS import entire file as a header - how do I resolve?

Cynthia_sas — Tue, 13 Mar 2018 14:07:27 GMT

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