I do have excel file which contains number in string format. Column header name is with space and I want to import file in SAS and convert string to numeric by using input function or something similar. SAS variable name should be sas naming format (space remove by _).
proc import out=test datafile='C:\desktop\test.xlsx' dbms=xlsx replace; getnames=yes; run; data test; set test; test_name = input('test name'n, best12.); run;
I need to create something like following output:
If the cells in the column in the Excel worksheet only contain numbers (whatever style is used to display them) then SAS will make a numeric variable from the column. Otherwise SAS will be forced to make a character variable so that it can hold the character cell values. Any cell that was a number will be converted to a digit string that represents the number in the cell (note this include dates).
If you set the VALIDVARNAME=V7 option then SAS should automatically try to convert the column headers into valid SAS names. It will only create variables with invalid names if you have (accidentally?) set the VALIDVARNAME option to ANY instead.
You can use the COMMA informat to convert strings that contain commas, dollar signs and percent signs into numbers. The INPUT() function does not care if width used on the informat specification is larger than the length of the string being read. 32 is the maximum width that the COMMA informat can handle.
data test_fixed; set test; test_name = input('test name'n, comma32.); run;
PS BEST is the name of FORMAT, not an INFORMAT. If you use it as an informat SAS will silently switch to using the normal numeric informat. Just like if you accidentally use DOLLAR as the name of an informat it will switch to using the COMMA informat.
proc import out=test datafile='C:\data\test.xlsx' dbms=xlsx replace; getnames=yes; run; data test (drop = test_name_import); set test (rename =(Test_name = test_name_import)); format test_name best12.; test_name = input(test_name_import, comma32.); run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.