DATA Step, Macro, Functions and more

Data Manage

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Data Manage

Dear Experts,

 

Got the following situation:

 

Data Have;

input D1-D10 $;

Cards;

80    81   32    45   22

75   10    12    65   82   45    20    82

15   80    32    21   45    50   32   19   23  75

83   22    19    25   32    10

12   75    34;

.... (about 30,000 rows)

I would like to convert the above data as - anywhere if there is 80 or 81 or 83 then it is h1, similarly where ever there is 23, 32, or 34 then it is f4 etc. At the end it would all be one letter and one number like:

h1   h1   f4     c2    f1

b2   a1   a1    c5    h1  c2   f2  h1

etc.

 

I tried like:

if D1 in ('80', '81', '83') then D1='h1'.

Since D2 or D3 or D4 etc. will also have a value either 80 or 81 or 83 then typing for D2 (and D3 and D4 etc.) like 'if D2 in ('80', '81, '83' ) then D2='h1' and son on and so forth is very cumbersome.

I am sure there are experts in the community who have better ideas to help me out  by eliminating 'if ... then' statementes for each variables and all its values.

 

Thank you.

Sijansap


Accepted Solutions
Solution
‎09-22-2017 01:15 PM
Super User
Posts: 13,321

Re: Data Manage

A typical solution for displaying a value based on another is to use a format.

proc format library=work;
value $mycode
'80','81','83' = 'h1'
'23','32','34' = 'f4'
;

Data Have;
   infile datalines missover;
   informat D1-D10 $5.; 
   input D1-D10 ;
format d1-d10 $mycode.;
Cards;
80    81   32    45   22   
75   10    12    65   82   45    20    82  
15   80    32    21   45    50   32   19   23  75
83   22    19    25   32    10   
12   75    34  
;
run;

You didn't really provide a rule for other values but I think this example is clear.

 

An advantage of this approach is that you can display the original value at any time incase you need to check the assignments just by changing the format to something like $2 in the table viewer or proc print.

Another advantage is that if you later need different groupings you can create an additional format an use that for a specific report or analysis without changing values or adding variables.

View solution in original post


All Replies
Solution
‎09-22-2017 01:15 PM
Super User
Posts: 13,321

Re: Data Manage

A typical solution for displaying a value based on another is to use a format.

proc format library=work;
value $mycode
'80','81','83' = 'h1'
'23','32','34' = 'f4'
;

Data Have;
   infile datalines missover;
   informat D1-D10 $5.; 
   input D1-D10 ;
format d1-d10 $mycode.;
Cards;
80    81   32    45   22   
75   10    12    65   82   45    20    82  
15   80    32    21   45    50   32   19   23  75
83   22    19    25   32    10   
12   75    34  
;
run;

You didn't really provide a rule for other values but I think this example is clear.

 

An advantage of this approach is that you can display the original value at any time incase you need to check the assignments just by changing the format to something like $2 in the table viewer or proc print.

Another advantage is that if you later need different groupings you can create an additional format an use that for a specific report or analysis without changing values or adding variables.

Contributor
Posts: 24

Re: Data Manage

Dear BallardW,
Thank you so much for your help. This format statement is much faster and easier for me to convert the data I have. I appreciate very much for your kindness and help.
Sijansap
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 114 views
  • 0 likes
  • 2 in conversation