topic Re: Proper formatting for input statement when creating dataset? in SAS Programming

Proper formatting for input statement when creating dataset?

RobertWF1 — Wed, 25 May 2022 18:45:17 GMT

I'm creating a test dataset. Usually the following input statement works fine, but this time SAS is incorrectly reading my input - it's blending variable values from different columns.

I've checked my syntax in the documentation but can't figure out what's going wrong.

data mbr_demo_test;
input member_id 7. first_attr_month $6. last_attr_month $6. first_eng_month $6. treatment $2. age_group $5. gender $1.;
datalines;
1234567 201701 202112 . No 70-74 M
2345678 201907 202009 202001 Y 65-69 F
;
run;

Re: Proper formatting for input statement when creating dataset?

RobertWF1 — Wed, 25 May 2022 19:08:27 GMT

Fixed the problem by removing the variable length values from the input statement:

data mbr_demo_test;
input member_id 7. first_attr_month $ last_attr_month $ first_eng_month $ treatment $ age_group $ gender $;
datalines;
1234567 201701 202112 . No 70-74 M2345678 201907 202009 202001 Y 65-69 F
;
run;

This works, although it adds a lot of padding (empty space) to each variable.

Re: Proper formatting for input statement when creating dataset?

Kurt_Bremser — Wed, 25 May 2022 19:13:39 GMT

Use the colon modifier in front of the formats, or the INPUT will not honor the delimiters.

Also use date informats for date values, and a character type for IDs.

input member_id :$7. first_attr_month :yymmn6. last_attr_month :yymmn6. first_eng_month :yymmn6. treatment :$2. age_group :$5. gender :$1.;
format first_attr_month last_attr_month first_eng_month yymmn6.;

Re: Proper formatting for input statement when creating dataset?

Kurt_Bremser — Wed, 25 May 2022 19:16:06 GMT

You also have 13 values in your dataline, but only 7 variables in your INPUT.

Re: Proper formatting for input statement when creating dataset?

Tom — Wed, 25 May 2022 22:15:29 GMT

Do you mean that the INPUT statement does not match how the data appears on the line?

I would agree that they don't match.

You are using fixed length formats to read the values. So just turn on the ruler line and check whether the values line up with the positions you are reading.

So it seem pretty clear there is an issue here. You read the first 7 characters into MEMBER_ID then read the next 6 into FIRST_ATTR_MONTH. But there is a space in the 8th position. So the 6 characters you read are the space and then the first 5 characters of the '201701' string.

Also you seem to have two observations worth of data on one line of data.

If you want to use fixed position input (formatted input) then align the data to the positions you are reading.

Or change the INPUT statement to use LIST mode. In that case make sure there is at least one space between values, no embedded spaces, and no completely empty values (use single period to mark missing values).

So either add the : prefix on (any needed) informats.

data mbr_demo_test;
input member_id first_attr_month :$6. last_attr_month :$6. first_eng_month :$6. treatment :$2. age_group :$5. gender :$1.;
datalines;
1234567 201701 202112      . No 70-74 M
2345678 201907 202009 202001  Y 65-69 F
;

Or just define the variables before using them in the input statement.

data mbr_demo_test;
  length member_id 8 first_attr_month last_attr_month first_eng_month $6 treatment $2 age_group $5 gender $1;
  inpput member_id -- gender;
datalines;
1234567 201701 202112      . No 70-74 M
2345678 201907 202009 202001  Y 65-69 F
;