I have this dataset:
data have1;
infile datalines;
format MailOrderIndicator $1. FilledMonth $7.;
input MailOrderIndicator $ FilledMonth $;
datalines;
N 2016M09
Y 2016M09
;
run;
Now, I want to convert it to this:
data have2;
infile datalines;
format MailOrderIndicator 1. FilledMonth 1.;
input MailOrderIndicator FilledMonth;
datalines;
0 9
1 9
;
run;
Which is to say, I want to create a new dataset out of the first one, but I want N to equal 0, Y to equal 1, and 2016M09 to equal 9 (Character values to numeric values). I tried a custom format but can't seem to get it to work. For example, I tried this:
proc format;
value $hello 'N'=0
'Y'=1;
run;
data want;
set have1;
format MailOrderIndicator $hello.;
run;
Which replaces the N with 0 and Y with 1 but they are still character, whereas I want them to be numeric...
The right side of a defined fomat must be a character litteral:
proc format;
value $hello 'N'='0'
'Y'='1';
run;
Then, as you want it be numeric you should do:
data want; set have1(rename=(
MailOrderIndicator=_mi));
MailOrderIndicator = input(put(_mi, $hello.),1.);
drop _mi;
run;
You can treat the FilledMonth in same way.
The right side of a defined fomat must be a character litteral:
proc format;
value $hello 'N'='0'
'Y'='1';
run;
Then, as you want it be numeric you should do:
data want; set have1(rename=(
MailOrderIndicator=_mi));
MailOrderIndicator = input(put(_mi, $hello.),1.);
drop _mi;
run;
You can treat the FilledMonth in same way.
You can't re-type a varaible from char to num or vice-versa, so you have to rename the vars, thereby freeing up the original varnames to use with the new var type. Below shows how:
The SCAN functions is asking for the 2nd "word" in col2, where it defines word-separators as the character "M".
regards,
Mark
data have1;
infile datalines;
format MailOrderIndicator $1. FilledMonth $7.;
input MailOrderIndicator $ FilledMonth $;
datalines;
N 2016M09
Y 2016M09
;
run;
data want;
set have1 (rename=(mailorderindicator=col1 filledmonth=col2));
mailorderindicator=ifn(col1='Y',1,0);
filledmonth=input(scan(col2,2,'M'),2.);
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.