BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

Hi all,

I have two datasets. Dataset 1 is at the person level, and each person between 1 and 15 variables containing values of a categorical variable. Basically, it's like this:

ID     Var1     Var2............Var15

A       3          40                 9

B     14           8                  .

The other dataset is essentially a map connecting values of variables 1-15 to a description.

Value      Desc

1             abc

2             def

3             ghi

...

100         zzz

How can I combine the datasets such that for each person's variables 1-15, there will be a description next to it of what that value means?

EG:

ID     Var1     Var1_desc     Var2     Var2_desc..........Var15     Var15_desc

A        3          ghi               40          xyz                    9          jkl

...and so on for each person.

4 REPLIES 4
Reeza
Super User

1. Create a format from your second data set.

2. Apply the format using put statements to get the new variables.

3. Consider just using the formatted values perhaps?

Walternate
Obsidian | Level 7

Would I have to do that by hand? The second dataset has over 1,000 variable descriptions.

art297
Opal | Level 21

No, you wouldn't have to create it by hand and, like Fareeza said, you may not have to or even want to create the extra 15 variables.  The following is one way to create and apply a format:

data codes;

  informat value 12.;

  informat Desc $50.;

  input Value Desc &;

  cards;

1             abc

2             def

3             ghi

8             something or other

9             ijk

14            something else

40            something else altogether

100           zzz

;

data ctrl;                                                                                                                            

  set codes (rename=(value=start

  Desc=label)) end=last;

  retain fmtname 'codes' type 'n';

  output;

  if last then do;

    hlo='O';

    label='***ERROR***';

    output;

  end;

run;

proc format library=work cntlin=ctrl;

run;

data have;

  input ID $ Var1-Var15;

  cards;

A  3 40 9  2 . . . . . . . . . . .

B 14  8 . 14 . . . . . . . . . . .

;

data want;

  set have;

  array vars(*) var1-var15;

  array var_desc(15) $50.;

  do _n_=1 to dim(vars);

    if not missing(vars{_n_}) then

     var_desc{_n_}=put(vars{_n_},codes.);

  end;

run;

data want;

  retain

  var1 var_desc1

  var2 var_desc2

  var3 var_desc3

  var4 var_desc4

  var5 var_desc5

  var6 var_desc6

  var7 var_desc7

  var8 var_desc8

  var9 var_desc9

  var10 var_desc10

  var11 var_desc11

  var12 var_desc12

  var13 var_desc13

  var14 var_desc14

  var15 var_desc16

  ;

  set want;

run;

Ksharp
Super User

or Hash Table . and reorder variables is also a problem.

data codes;
  informat value 12.;
  informat Desc $50.;
  input Value Desc &;
  cards;
1             abc
2             def
3             ghi
8             something or other
9             ijk
14            something else
40            something else altogether
100           zzz
;
data have;
  input ID $ Var1-Var15;
  cards;
A  3 40 9  2 . . . . . . . . . . .
B 14  8 . 14 . . . . . . . . . . .
;
run;
data x;
 set have;
if _n_ eq 1  then do;
  if 0 then set codes;
  declare hash h(dataset:'codes');
   h.definekey('value');
   h.definedata('desc');
   h.definedone();
end;
 array v{*} var1-var15;
 n=0;
 do i=1 to dim(v);
  n+1;value=v{i};call missing(desc);rc=h.find();output;     
 end;
 drop var: i rc;
 run;
proc sql noprint;
 select distinct catt('x(where=(n=',put(n,best8. -l),') rename=(value=value_',put(n,best8. -l),' desc=desc_',put(n,best8. -l),'))') into : list separated by ' ' 
  from x
   order by n;
quit;
%put &list ;
data want;
 merge &list ;
 by id ;
 drop n;
run;

Xia Keshan

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 955 views
  • 0 likes
  • 4 in conversation