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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.