BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sijansap
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

2 REPLIES 2
ballardw
Super User

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.

sijansap
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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