Help using Base SAS procedures

Format Conversion Issue

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Format Conversion Issue

 

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

 

 


Accepted Solutions
Solution
‎11-20-2016 04:54 PM
Trusted Advisor
Posts: 1,584

Re: Format Conversion Issue

Posted in reply to JediApprentice

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.

 

 

View solution in original post


All Replies
Solution
‎11-20-2016 04:54 PM
Trusted Advisor
Posts: 1,584

Re: Format Conversion Issue

Posted in reply to JediApprentice

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.

 

 

Trusted Advisor
Posts: 1,022

Re: Format Conversion Issue

Posted in reply to JediApprentice

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;
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 209 views
  • 0 likes
  • 3 in conversation