Numeric Replacement Column

Accepted Solution Solved
Reply
Super Contributor
Posts: 497
Accepted Solution

Numeric Replacement Column

I’m trying create a numeric column that represents a character column that I can format back to a character column.  I can think of a few ways to convert a column to numeric but none of them seem to be efficient.  Please advise on how to create the numeric column.

For example

Orsales:

Product_line product_line_as_num

Children        1

Children        1

Sports           4

Outdoors       3

 

Below is the code to format back to character.

I’m following:

http://support.sas.com/techsup/notes/v8/25/054.html

Below is a block of code that creates a format for all distinct values of sashelp.orsales

proc sql;

     create table testData as

     select distinct Product_Line

     from sashelp.orsales;

    

     create table testData2 as

     select monotonic() as row, *

     from testData;

quit;

 

data formatDataSet;                                                                                                                               

   length label $ 20;                                                                                                                  

   set testData2 end=last;                                                                                                                    

   fmtname='fmtOne';                                                                                                                   

   start=row;                                                                                                                            

   label=Product_Line;                                                                                                                             

   output;                                                                                                                                                                                                                                                    

run

 

proc print data=formatDataSet;

run;

 

proc format cntlin=formatDataSet fmtlib;                                                                                                         

run;


Accepted Solutions
Solution
‎04-06-2016 01:17 PM
Super User
Posts: 11,343

Re: Numeric Replacement Column

Posted in reply to DavidPhillips2

Is this sufficieint?

proc sql;
     create table testData as
     select distinct Product_Line
     from sashelp.orsales;
    
     create table testData2 as
     select monotonic() as row, *
     from testData;
quit;
 
data formatDataSet; 
   length label $ 20 ;
   set testData2 end=last;
   fmtname='InfmtOne';
   label=row;
   type='I'; /* informat*/
   start=Product_Line;
   output;
   fmtname='fmtOne';
   start=row;
   type='N';
   label=Product_Line;
   output;
run; 
proc sort data=formatDataSet;
   by fmtname start;
run;

proc format library=work cntlin=formatdataset;
run;


Data example;
   informat product_line InfmtOne.;
   infile datalines dsd;
   input product_line;
datalines;
Outdoors  
Sports   
Children  
Clothes & Shoes
;
run;

proc print data=example;
   var product_line;
   format product_line fmtOne.;
run;

BTW, Please post code into a code box. Your posts have lots of unprintable non-displayed characters when posted as html that require extensive editing to run.

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Numeric Replacement Column

Posted in reply to DavidPhillips2

I would be very tempted to use the data set to create the format to reverse the positions of label and start to create an INFORMAt as well.

 

Then the creation of numeric could either be done when reading initial data or

 

product_line_as_num = input(Product_line ,InFmtOne.); 

 

Super Contributor
Posts: 497

Re: Numeric Replacement Column

Ballard, please post an example using an informat.

Solution
‎04-06-2016 01:17 PM
Super User
Posts: 11,343

Re: Numeric Replacement Column

Posted in reply to DavidPhillips2

Is this sufficieint?

proc sql;
     create table testData as
     select distinct Product_Line
     from sashelp.orsales;
    
     create table testData2 as
     select monotonic() as row, *
     from testData;
quit;
 
data formatDataSet; 
   length label $ 20 ;
   set testData2 end=last;
   fmtname='InfmtOne';
   label=row;
   type='I'; /* informat*/
   start=Product_Line;
   output;
   fmtname='fmtOne';
   start=row;
   type='N';
   label=Product_Line;
   output;
run; 
proc sort data=formatDataSet;
   by fmtname start;
run;

proc format library=work cntlin=formatdataset;
run;


Data example;
   informat product_line InfmtOne.;
   infile datalines dsd;
   input product_line;
datalines;
Outdoors  
Sports   
Children  
Clothes & Shoes
;
run;

proc print data=example;
   var product_line;
   format product_line fmtOne.;
run;

BTW, Please post code into a code box. Your posts have lots of unprintable non-displayed characters when posted as html that require extensive editing to run.

 

Super Contributor
Posts: 497

Re: Numeric Replacement Column

[ Edited ]

I’m trying to figure out how to use informat to add a numeric column for product line in a copy of sashelp.orsales.

 

found how to use the format in a dataset

data outputTest; set sashelp.orsales;
	product_line2 = input(product_line, InfmtOne.);
run;

Thanks for the tip about the code box.  I didn't know about the feature.

Super User
Posts: 10,041

Re: Numeric Replacement Column

Posted in reply to DavidPhillips2
It is Hash Table thing.



data have;
input Product_line $;
cards;
Children        
Children        
Sports           
Outdoors  
Children
XX        
;
run;
data want;
 if _n_ eq 1 then do;
  if 0 then set have;
  declare hash h();
  h.definekey('Product_line');
  h.definedata('number');
  h.definedone();
 end;
set have;
if h.find() ne 0 then do;
 n+1;number=n;h.add();
end;
drop n;
run;


☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 397 views
  • 1 like
  • 3 in conversation