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

[belboy]

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.

[Tom]

Don't ask the same question multiple times.

[belboy]

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

[Cynthia_sas]

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]

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]

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

[belboy]

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]

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]

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]

Hi:

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

Cynthia

[belboy]

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]

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