topic Re: Create a data set in SAS in SAS Programming

Create a data set in SAS

Ronein — Mon, 01 Aug 2022 04:31:24 GMT

Hello,

Someone sent me an excel file with 3 columns.

He told me that he cannot perform calculations on these columns.

I checked and I saw that the format of the columns in Excel is "General" and I have tried to convert it to "Number" format but it didn't work.

I thought that it will be a good idea to import the table into SAS and than export it into excel and see get the 3 columns as Number format in excel.

I am trying to create a data set in SAS but it is not working, I get a data set with null values.

What is the way to do it correctly please?

Data have;
Input X1 X2 X3;
cards;
 22,532,091.84 97,797.96 9,338,743.32
 15,573,220.09 107,197.59 3,253,073.34
 17,601,372.44 170,123.33 2,157,860.99
 15,948,182.53 172,386.86 1,047,729.41
 10,297,115.52 162,988.85 570,263.86
 15,617,336.02 269,261.03 473,274.83
 10,795,404.83 330,659.42 274,623.30
 9,010,041.04 452,650.78 155,526.25
 2,389,875.61 217,180.51 20,502.96
 861,068,080.33 5,196,905.31 44,344.63
 16,385,555.20 38,409.44 76,656.82
;
Run;

Re: Create a data set in SAS

PeterClemmensen — Mon, 01 Aug 2022 04:35:30 GMT

1) If you can figure it out directly in Excel, do that.

2) I don't see how creating a SAS data set with datalines / cards relates to importing an Excel sheet as data?

Re: Create a data set in SAS

Kurt_Bremser — Mon, 01 Aug 2022 04:38:40 GMT

Your numbers cannot be read with the default informat, you need to use the COMMAw. informat.

Re: Create a data set in SAS

Ronein — Mon, 01 Aug 2022 14:01:36 GMT

I would like to create a data set by using cards statement and enter the values as I did.
May you please help to create the data set correctly because now the values in data set are null

Re: Create a data set in SAS

Kurt_Bremser — Mon, 01 Aug 2022 14:04:50 GMT

Usage of informat:

Input (X1 X2 X3) (:comma20.);

This is all it takes.

Re: Create a data set in SAS

Tom — Mon, 01 Aug 2022 14:18:21 GMT

To read strings with thousand separators as numbers use the COMMA informat.

data have;
  input X1 X2 X3;
  informat x1-x3 comma.;
cards;
 22,532,091.84 97,797.96 9,338,743.32
 15,573,220.09 107,197.59 3,253,073.34
 17,601,372.44 170,123.33 2,157,860.99
 15,948,182.53 172,386.86 1,047,729.41
 10,297,115.52 162,988.85 570,263.86
 15,617,336.02 269,261.03 473,274.83
 10,795,404.83 330,659.42 274,623.30
 9,010,041.04 452,650.78 155,526.25
 2,389,875.61 217,180.51 20,502.96
 861,068,080.33 5,196,905.31 44,344.63
 16,385,555.20 38,409.44 76,656.82
;

If you want the values to display with the thousand separator also then you might want to attach the COMMA format to the variables. Make sure to count the decimal point and commas when calculating the width to use.

format x1-x3 comma16.2 ;