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;
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?
Usage of informat:
Input (X1 X2 X3) (:comma20.);
This is all it takes.
Your numbers cannot be read with the default informat, you need to use the COMMAw. informat.
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 ;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: