DATA Step, Macro, Functions and more

convert numeric to character excel import file

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

convert numeric to character excel import file

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

 

 


Accepted Solutions
Solution
‎04-13-2018 08:26 AM
Super User
Posts: 10,574

Re: convert numeric to character excel import file


@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 24,010

Re: convert numeric to character excel import file

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

 

 


 

Super User
Posts: 4,025

Re: convert numeric to character excel import file

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.

Solution
‎04-13-2018 08:26 AM
Super User
Posts: 10,574

Re: convert numeric to character excel import file


@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 184

Re: convert numeric to character excel import file

Posted in reply to KurtBremser

"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. 

Super User
Posts: 24,010

Re: convert numeric to character excel import file

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. 


 

Regular Contributor
Posts: 184

Re: convert numeric to character excel import file

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!

Super User
Posts: 24,010

Re: convert numeric to character excel import file

Hold down ALT while selecting the code from the log to select the code WITHOUT the line numbers.

Regular Contributor
Posts: 184

Re: convert numeric to character excel import file

thanks for the heads up. 

Super User
Super User
Posts: 8,279

Re: convert numeric to character excel import file

[ Edited ]

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 ;

 

Super User
Super User
Posts: 8,279

Re: convert numeric to character excel import file

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.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 390 views
  • 6 likes
  • 6 in conversation