topic Re: Truncating when reading in csv format data file in SAS Programming

Truncating when reading in csv format data file

Amy_W — Mon, 04 Aug 2014 15:06:42 GMT

Hello,

I have a csv format data file that I'd like to read into SAS. Variables vary different length in the original data file. For example,

"parfteoy","MAT04C2","MA","01010000","01010032","102309025","","00068023639805201406","MP09","1"

"parfteoy","MAT07C2","MA","01010000","01010310","102436546","Mozilla/5.0 (X11; CrOS x86_64 5500.130.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.134 Safari/537.36","AAAKK14142P000206919","MC01","A"

I used data step to read in the file, and here is my codes.

data EOY_survey1;

infile '....EOY_AL.txt' dsd missover lrecl=600;

input Test_Admin_Code $ Test_Code $ State $ District $ School $ PA_Unique_ID $ Operating_System $ Clip_UIN $ Survey_Item_UIN $ Survey_Response $;

run;

No error messages but variables were truncated in Operating_System and Survey_Response. I have tried to set up the length but it worked only for Survey_Response, not for Operating_System. Could someone give me suggestions?

Thanks a lot!

Re: Truncating when reading in csv format data file

data_null__ — Mon, 04 Aug 2014 15:13:42 GMT

Looks like all your variables being define with length $8

Add a length statement to define the length of the variables that need to be longer than 8 bytes.

Re: Truncating when reading in csv format data file

RW9 — Mon, 04 Aug 2014 15:15:15 GMT

Hi,

Yes, two things:

data eoy_survey1;

attrib test_admin_code test_code ... format=$2000.; /* If you don't attrib or length first then these get defaulted to char(8) */

infile ... dlm="," lrecl=32000; /* Update to be longer it doesn't hurt, also I like specifying the delimiter, though it shouldn't matter */

input xyz $...;

run;

Re: Truncating when reading in csv format data file

Amy_W — Mon, 04 Aug 2014 15:27:56 GMT

Thanks to all.

The attrib statment worked but not the length statement. I will dig more. Thanks again.

Re: Truncating when reading in csv format data file

jwillis — Mon, 04 Aug 2014 15:31:10 GMT

Amy_W,

This worked for me. I added a delimeter statement and gave all the variables lengths.

data EOY_survey1;

infile 'E:\test.csv' dsd delimiter=',' truncover nopad lrecl=600;
length Test_Admin_Code Test_Code State District School PA_Unique_ID $50;

length Operating_System $150;
length Clip_UIN $50 Survey_Item_UIN $50 Survey_Response $120;
input Test_Admin_Code $ Test_Code $ State $ District $ School $ PA_Unique_ID $ Operating_System $ Clip_UIN $ Survey_Item_UIN $ Survey_Response $;

run;

proc print data=EOY_survey1;
run;

Re: Truncating when reading in csv format data file

Amy_W — Mon, 04 Aug 2014 15:44:07 GMT

Thanks jwillis.

I tried the length statement at the very beginning to specify Operating_System and Survey_Response only. However, variables after Operating_System were all missing. I didn't try specify length to every variable. Maybe that's the problem.

Re: Truncating when reading in csv format data file

data_null__ — Mon, 04 Aug 2014 15:56:17 GMT

I would not use a FORMAT to imply length in this way, it makes it look like you don't know what you're doing. I usually prefer that character variables not have simple $Fnnn. format associated.

Re: Truncating when reading in csv format data file

ballardw — Mon, 04 Aug 2014 16:41:13 GMT

And if you specify a length it will be assigned a $ format of that length by default.