topic Re: Format Conversion Issue in SAS Procedures

Format Conversion Issue

JediApprentice — Sat, 19 Nov 2016 00:03:56 GMT

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

Re: Format Conversion Issue

Shmuel — Sat, 19 Nov 2016 00:26:32 GMT

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.

Re: Format Conversion Issue

mkeintz — Sat, 19 Nov 2016 00:29:28 GMT

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;