Hello-,
I have a list of variable that are supposed to be numeric but they appeared as characters. Below is the list of variable. I want to change all to numeric while keeping the name. Doing one at a time is boring .
# | Variable | Type | Len | Format | Informat | Label |
---|---|---|---|---|---|---|
64 | AA | Char | 1 | $1. | $1. | AA |
65 | AB | Char | 1 | $1. | $1. | AB |
86 | AC | Char | 1 | $1. | $1. | AC |
87 | AD | Char | 1 | $1. | $1. | AD |
88 | AE | Char | 1 | $1. | $1. | AE |
89 | AF | Char | 1 | $1. | $1. | AF |
31 | AG | Char | 105 | $105. | $105. | AG |
32 | AH | Char | 43 | $43. | $43. | AH |
33 | AI | Char | 1 | $1. | $1. | AI |
34 | AJ | Char | 1 | $1. | $1. | AJ |
35 | AK | Char | 1 | $1. | $1. | AK |
36 | AL | Char | 1 | $1. | $1. | AL |
37 | AM | Char | 1 | $1. | $1. | AM |
90 | AN | Char | 1 | $1. | $1. | AN |
91 | AO | Char | 1 | $1. | $1. | AO |
92 | AP | Char | 1 | $1. | $1. | AP |
93 | AQ | Char | 1 | $1. | $1. | AQ |
94 | AR | Char | 1 | $1. | $1. | AR |
95 | AS | Char | 1 | $1. | $1. | AS |
96 | AT | Char | 1 | $1. | $1. | AT |
97 | AU | Char | 1 | $1. | $1. | AU |
98 | AV | Char | 1 | $1. | $1. | AV |
99 | AW | Char | 1 | $1. | $1. | AW |
100 | AX | Char | 1 | $1. | $1. | AX |
101 | AY | Char | 1 | $1. | $1. | AY |
Can you help with the looping code?
Can you show anything you've tried so far? We're happy to help but this is asked often on here so there should be quite a few good options to get you started.
This isn't an optimal solution IMO but you could easily write a macro that does it, especially if you know how to do it manually.
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md
EDIT: If you check the Related Topics on this post, left hand side of the board, the first entry shows how to do exactly what you're asking.
It might be possible (or it might not be possible) to modify how the data set was created so it will contain numeric values rather than character values. So, how was this data set created?
They are excel data set.
@desireatem wrote:
They are excel data set.
How do you turn the Excel into SAS? Please show us the code you used.
@desireatem wrote:
Hello-,
I have a list of variable that are supposed to be numeric but they appeared as characters. Below is the list of variable. I want to change all to numeric while keeping the name. Doing one at a time is boring .
# Variable Type Len Format Informat Label 64 AA Char 1 $1. $1. AA 65 AB Char 1 $1. $1. AB 86 AC Char 1 $1. $1. AC 87 AD Char 1 $1. $1. AD 88 AE Char 1 $1. $1. AE 89 AF Char 1 $1. $1. AF 31 AG Char 105 $105. $105. AG 32 AH Char 43 $43. $43. AH 33 AI Char 1 $1. $1. AI 34 AJ Char 1 $1. $1. AJ 35 AK Char 1 $1. $1. AK 36 AL Char 1 $1. $1. AL 37 AM Char 1 $1. $1. AM 90 AN Char 1 $1. $1. AN 91 AO Char 1 $1. $1. AO 92 AP Char 1 $1. $1. AP 93 AQ Char 1 $1. $1. AQ 94 AR Char 1 $1. $1. AR 95 AS Char 1 $1. $1. AS 96 AT Char 1 $1. $1. AT 97 AU Char 1 $1. $1. AU 98 AV Char 1 $1. $1. AV 99 AW Char 1 $1. $1. AW 100 AX Char 1 $1. $1. AX 101 AY Char 1 $1. $1. AY
How did they "appear as character"?
I would suggest that you may want to read the data correctly. I see that sort of thing happen when relying on proc import, which guesses variable type and length based on examining a very few rows of data by default. The $1. informat and format would make me believe that for the first rows of the data source you had a lot of blank values.
Since you only have 1 character in the data set then "changing" them to numeric is only practical if every single one of those values was only supposed to have exactly one digit. If there are values that should contain decimals or are larger than 9 then you need to go back and reread the data so the values come in correctly. Either modify the settings for proc import if the file was delimited to add a large value to the Guessingrows option or if the file was a spreadsheet save it as CSV and import with a large guessingrows value so more rows are examined before setting properties. Or write a data step to read.
Additionally, your variables AG and AH are too long to create reliable "numeric" values.
@Reeza wrote:
Given the variable names, I'm guessing Excel was the original data source.
Agree that seems very likely.
While I agree with my colleagues that it would be nice to see what you've already tried, I do understand that this type of coding is probably difficult for someone who has never done it before.
The following guesses what your two files might look like (just do have some test data), the uses proc sql to create macro variables that will be used in two data steps to (1) add underscores to the start of each numeric variable ; (2) create the desired numeric variables; (3) assign the variable labels to the newly created variables; (4) reorder the variables to their original order.
You may have to do something more with the two long character variables, possibly including the STRIP function in the step that calculates the variables.
data variables;
infile cards dlm='09'x;
input vnum Variable $ Type $ Len Format $ Informat $ Label $;
cards;
64 AA Char 1 $1. $1. AA
65 AB Char 1 $1. $1. AB
86 AC Char 1 $1. $1. AC
87 AD Char 1 $1. $1. AD
88 AE Char 1 $1. $1. AE
89 AF Char 1 $1. $1. AF
31 AG Char 105 $105. $105. AG
32 AH Char 43 $43. $43. AH
33 AI Char 1 $1. $1. AI
34 AJ Char 1 $1. $1. AJ
35 AK Char 1 $1. $1. AK
36 AL Char 1 $1. $1. AL
37 AM Char 1 $1. $1. AM
90 AN Char 1 $1. $1. AN
91 AO Char 1 $1. $1. AO
92 AP Char 1 $1. $1. AP
93 AQ Char 1 $1. $1. AQ
94 AR Char 1 $1. $1. AR
95 AS Char 1 $1. $1. AS
96 AT Char 1 $1. $1. AT
97 AU Char 1 $1. $1. AU
98 AV Char 1 $1. $1. AV
99 AW Char 1 $1. $1. AW
100 AX Char 1 $1. $1. AX
101 AY Char 1 $1. $1. AY
;
data have;
input (BA BB BC BD BE BF BG BH BI BJ
BK BL BM BN BO BP BQ BR BS BT
BU BV BW BX BY BZ CA CB CC CD) ($1.)
(AG AH AI AJ AK AL AM) ($1.)
(DA DB DC DD DE DF DG DH DI DJ
DK DL DM DN DO DP DQ DR DS DT
DU DV DW DX DY DZ) ($1.)
(AA AB) ($1.)
(EA EB EC ED EE EF EG EH EI EJ
EK EL EM EN EO EP EQ ER ES ET) ($1.)
(AC AD AE AF) ($1.)
(AN AO AP AQ AR AS AT AU AV AW
AX AY) ($1.)
(FA FB) ($1.)
;
cards;
1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
;
proc sql noprint;
select catx('=_',variable,variable),
catt(variable,'=input(_',variable,',8.);'),
catx(' ','label',variable,'=',Label,';')
into :renames separated by ' ',
:makenum separated by ' ',
:labels separated by ' '
from variables
;
select name
into :varorder separated by ' '
from dictionary.columns
where libname='WORK' and
memname='HAVE'
order by varnum
;
quit;
data want (drop=_:);
set have(rename=(&renames.));
&makenum.
&labels.
run;
data want;
retain &varorder.;
set want;
run;
Art, CEO, AnalystFinder.com
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.