DATA Step, Macro, Functions and more

Truncating when reading in csv format data file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Truncating when reading in csv format data file

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"

"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","MC18","Some of the questions I just didn't understand what it was asking me to do."

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!


Accepted Solutions
Solution
‎08-04-2014 11:15 AM
Super User
Super User
Posts: 7,970

Re: Truncating when reading in csv format data file

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: Truncating when reading in csv format data file

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.

Occasional Contributor
Posts: 9

Re: Truncating when reading in csv format data file

Posted in reply to data_null__

Thanks to all.

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

Regular Contributor
Posts: 217

Re: Truncating when reading in csv format data file

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;

Occasional Contributor
Posts: 9

Re: Truncating when reading in csv format data file

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.

Solution
‎08-04-2014 11:15 AM
Super User
Super User
Posts: 7,970

Re: Truncating when reading in csv format data file

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;

Respected Advisor
Posts: 3,799

Re: Truncating when reading in csv format data file

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.

Super User
Posts: 11,343

Re: Truncating when reading in csv format data file

Posted in reply to data_null__

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 448 views
  • 3 likes
  • 5 in conversation