topic Data Manage in SAS Programming

Data Manage

sijansap — Fri, 22 Sep 2017 16:10:52 GMT

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

Re: Data Manage

ballardw — Fri, 22 Sep 2017 16:53:36 GMT

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.

Re: Data Manage

sijansap — Fri, 22 Sep 2017 17:14:22 GMT

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