topic Reading Data into SAS - Missing Some Data and Separated with Commas in SAS Programming

Reading Data into SAS - Missing Some Data and Separated with Commas

eslb2022 — Mon, 22 Nov 2021 22:33:20 GMT

I have a data set in a file that is separated with commas, missing data is simply left blank, and is made of characters and numbers. I need to read the data file into SAS and print the data set, but I believe the commas and missing data is not being read into SAS properly. I have attached a photo of the data set, the code I used, and the table SAS generated. How do I fix the code to read the data properly?

Code:

DATA BenAndJerrys;
INFILE 'C:\Users\165\Downloads\BenAndJerrys.dat';
INPUT FlavorName $ PortionSize Calories CaloriesFromFat Fat SaturatedFat TransFat Cholesterol Sodium TotalCarbohydrates DietaryFiber Sugars Proteins YearIntroduced YearRetired ContentDescription $ Notes $ ;
PROC PRINT;
RUN;

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

Astounding — Mon, 22 Nov 2021 21:45:56 GMT

Where you have multiple commas in a row, and each marks a separate variable, you can tell SAS to expect that and accomodate it by adding dsd:

INFILE 'C:\Users\lgb156\Downloads\BenAndJerrys.dat' dsd;

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

sbxkoenk — Mon, 22 Nov 2021 21:47:43 GMT

Hello,

Can you try to put the DSD option on the INFILE statement?

The DSD option sets the comma as the default delimiter and if there are two adjacent commas it means two delimiters (a missing value is supposed to be in-between the two commas).

Koen

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

eslb2022 — Mon, 22 Nov 2021 22:07:36 GMT

That worked, but the flavor name and description columns will not fill with the entire context. The image shows that the Flavor Name for "Banana Split" only says "Banana: in the cell. How do I tell SAS to put the entire context in the cell?

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

sbxkoenk — Mon, 22 Nov 2021 22:13:42 GMT

Hello,

The default delimiter is a blank, so that's why it outputs "Banana" to your first variable and not "Banana Split".

With adding DSD option on the INFILE statement, you should also solve that, no??

If not we can work with an informat.

Koen

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

Tom — Mon, 22 Nov 2021 22:29:13 GMT

You didn't define your variables before using them in the INPUT statement. So SAS was forced the GUESS how to define them based on how you used them first. Your input statement provides no other information it can use to guess other than the $ modifier so it will default all of the variables to use a length of 8. The ones with the $ after them it will default to character. Any variable that did not have the $ modifier after it will default to numeric.

Add a LENGTH statement before the INPUT statement to set the length (and type) of the variables. Once the type is known you don't need the $ modifiers in the INPUT statement anymore.

Re: Reading Data into SAS - Missing Some Data and Separated with Commas

Tom — Mon, 22 Nov 2021 22:32:38 GMT

You could also add informats in the INPUT to give SAS some additional information to base its guess of how to define the variables. But make sure to use the : modifier so that the input statement does not read past or stop short of the delimiters.

DATA BenAndJerrys;
  INFILE 'C:\Users\lgb156\Downloads\BenAndJerrys.dat' dsd truncover;
  INPUT FlavorName :$30. PortionSize Calories CaloriesFromFat Fat SaturatedFat 
        TransFat Cholesterol Sodium TotalCarbohydrates DietaryFiber Sugars 
        Proteins YearIntroduced YearRetired 
        ContentDescription :$50. Notes :$200. 
   ;
run;