Hi,
I used proc import to transfer data to SAS. One of my variable ID (8 digit number) was transferred as numeric variable.
When I used input function to convert it to character variable by using
character=input(numeric, 8.);
it gives me a value of 0. I also tried using strip function before input but that gave me the same result.
I want to convert a number (12345678) to character(12345678)
Does anyone have a suggestion?
Thanks
@leahcho wrote:
Hi,
I used proc import to transfer data to SAS.
This is where your problems start. proc import makes guesses about column attributes, and those guesses depend on content.
Use a file format that gives you control, and import data with the proper means (data step). proc import can help you initially by providing a basic data step that you can adapt to your needs; it is in no way suited for repeatable production-type work.
It's PUT() not INPUT() in this case.
Try:
data class;
set sashelp.class;
age_char = put(age, 8.);
run;
proc print data=class;
run;
@leahcho wrote:
Hi,
I used proc import to transfer data to SAS. One of my variable ID (8 digit number) was transferred as numeric variable.
When I used input function to convert it to character variable by using
character=input(numeric, 8.);
it gives me a value of 0. I also tried using strip function before input but that gave me the same result.
I want to convert a number (12345678) to character(12345678)
Does anyone have a suggestion?
Thanks
An alternative approach is to use the "Convert to text" function in Excel first, then re-import it into SAS, or Save As CSV in Excel and re-import. Obviously not an option if you have a lot of spreadsheets.
@leahcho wrote:
Hi,
I used proc import to transfer data to SAS.
This is where your problems start. proc import makes guesses about column attributes, and those guesses depend on content.
Use a file format that gives you control, and import data with the proper means (data step). proc import can help you initially by providing a basic data step that you can adapt to your needs; it is in no way suited for repeatable production-type work.
"import data with the proper means (data step)"
Hey, i've never seen this done before, but I am interested. What would the code (in some generic form) look like if you were to import data with a data step? I've only ever used proc import.
Check your log after a PROC IMPORT with a text or delimited file.
@sastuck wrote:
"import data with the proper means (data step)"
Hey, i've never seen this done before, but I am interested. What would the code (in some generic form) look like if you were to import data with a data step? I've only ever used proc import.
data FEB13.WAGE1 ;
87 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
88 infile '~/425/425_in_class_exercise/wage1.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
89 informat wage best32. ;
90 informat educ best32. ;
91 informat exper best32. ;
92 informat tenure best32. ;
93 informat nowhite best32. ;
94 informat female best32. ;
95 informat married best32. ;
96 informat numdep best32. ;
97 informat smsa best32. ;
98 informat northcen best32. ;
99 informat south best32. ;
100 informat west best32. ;
101 informat construc best32. ;
102 informat ndurman best32. ;
103 informat trcommpu best32. ;
104 informat trade best32. ;
105 informat services best32. ;
106 informat profserv best32. ;
107 informat profocc best32. ;
108 informat clerocc best32. ;
109 informat servocc best32. ;
110 informat lwage best32. ;
111 informat expersq best32. ;
112 informat tenursq best32. ;
113 format wage best12. ;
114 format educ best12. ;
115 format exper best12. ;
116 format tenure best12. ;
117 format nowhite best12. ;
118 format female best12. ;
119 format married best12. ;
120 format numdep best12. ;
121 format smsa best12. ;
122 format northcen best12. ;
123 format south best12. ;
124 format west best12. ;
125 format construc best12. ;
126 format ndurman best12. ;
127 format trcommpu best12. ;
128 format trade best12. ;
129 format services best12. ;
130 format profserv best12. ;
131 format profocc best12. ;
132 format clerocc best12. ;
133 format servocc best12. ;
134 format lwage best12. ;
135 format expersq best12. ;
136 format tenursq best12. ;
137 input
138 wage
139 educ
140 exper
141 tenure
142 nowhite
143 female
144 married
145 numdep
146 smsa
147 northcen
148 south
149 west
150 construc
151 ndurman
152 trcommpu
153 trade
154 services
155 profserv
156 profocc
157 clerocc
158 servocc
159 lwage
160 expersq
161 tenursq
162 ;
163 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
164 run;
Oh cool. So If just ran this first thing in my program (after the lib statement) it would import the data? That's revolutionary!
Hold down ALT while selecting the code from the log to select the code WITHOUT the line numbers.
thanks for the heads up.
PROC IMPORT writes really ugly SAS code. There is no need for either an INFORMAT or a FORMAT for SAS to read most data. The main exceptions are date, time and datetime values. It uses the deprecated MISSOVER option instead of the newer more flexible TRUNCOVER option.
Your example is just using all numeric variables so the code could be as simple as.
data FEB13.WAGE1 ;
infile '~/425/425_in_class_exercise/wage1.csv' truncover dsd firstobs=2 ;
input wage educ exper tenure nowhite female married numdep smsa
northcen south west construc ndurman trcommpu trade services
profserv profocc clerocc servocc lwage expersq tenursq
;
run;
If one or more of the variables are character then instead use a LENGTH statement to define the variables and a simplified INPUT statement. For example if SMSA should be stored as a ten character long string then you could replace the INPUT statement above with these two statements.
length wage educ exper tenure nowhite female married numdep 8
smsa $10
northcen south west construc ndurman trcommpu trade services
profserv profocc clerocc servocc lwage expersq tenursq 8
;
input wage -- tenursq ;
Fix the spreadsheet so that the cells in that column are defined as character and the problem will go away.
The INPUT() function is used to convert TEXT into values. To convert values into text you want to use the PUT() function.
If the values should be 8 digit numbers then use the Z8. format so that all values will have 8 characters.
character = put(numeric,z8.);
If you want to use the normal 8. format (also known as the F8. format) then you could end up with leading spaces, which is generally a bad idea. You can add the -L option to the format to left align the value.
character = put(numeric,8.-L);
The reason you had problems is that SAS had to first convert your numeric variable to character so it could use it as the input for the INPUT() function. And then SAS had to again convert the new number the INPUT() function generated to character to store into the character variable you wanted to create.
SAS will use the BEST12. format when it does those automatic type conversions. So if you started with an 8 digit number like 12,345,678 then the first conversion would generate the string ' 12345678' which has four leading spaces. Then the INPUT() function would read the first 8 of those and generate the number 1,234. Which would then be converted into the string ' 1234' which as 8 leading spaces. If you then assigned that value to a character variable of length 8 all you would get would be the spaces.
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.