DATA Step, Macro, Functions and more

How do I create labels for more than 400 variables !?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

How do I create labels for more than 400 variables !?

I have two excel files. One has the data set with 10000 rows & more than 400 variables. Second excel file has data dictionary with variable name (in first excel file) & labels. How can I label all my variables at one go with in SAS ? 
The conventional method which I know is the below which is time taking and hectic for all 400 variables.
data work.Sample ;
set class1.TestData;
label storeid = 'Store ID';

label storeName = 'Name of the store';

.....

run;

 

I have intermidiate knowledge in Proc SQL & Macros. Please advise.


Accepted Solutions
Solution
‎03-03-2016 03:23 AM
Super User
Posts: 9,682

Re: How do I create labels for more than 400 variables !?


data have;
 set sashelp.class;
run;





data meta;
input name : $20. label  $40.;
cards;
sex asfasd sdfasdf
name sfsefl
;
run;
data _null_;
 set meta end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist nodetails; modify have; label');
 call execute(cats(name,'="',label,'"'));
 if last then call execute(';quit;');
run;

View solution in original post


All Replies
Super User
Posts: 5,257

Re: How do I create labels for more than 400 variables !?

IMO having a data set with 400 variables makes little sense.
I would transpose it and potentially normalize the data structure. Then you could either build formats from or join your data dictionary with the "One" data set.
Data never sleeps
Occasional Contributor
Posts: 6

Re: How do I create labels for more than 400 variables !?

Thank you LinusH. I have another one, even if I normalize & apply joins, how can label be associated with variables ? don't we use the label statement ?
Solution
‎03-03-2016 03:23 AM
Super User
Posts: 9,682

Re: How do I create labels for more than 400 variables !?


data have;
 set sashelp.class;
run;





data meta;
input name : $20. label  $40.;
cards;
sex asfasd sdfasdf
name sfsefl
;
run;
data _null_;
 set meta end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist nodetails; modify have; label');
 call execute(cats(name,'="',label,'"'));
 if last then call execute(';quit;');
run;
Occasional Contributor
Posts: 6

Re: How do I create labels for more than 400 variables !?

[ Edited ]

Thank you LinusH & Ksharp. Working on Ksharp's solution.

Occasional Contributor
Posts: 6

Re: How do I create labels for more than 400 variables !?

It worked. Thank you. Could you please brief me a little theortical explanation on your solution ?

Super User
Posts: 9,682

Re: How do I create labels for more than 400 variables !?

Once you saw the sas code generated by SAS in LOG, You will know what I am trying to do .

Super User
Posts: 5,257

Re: How do I create labels for more than 400 variables !?

If make rows fro columns your intended labels for column names are now values on rows (some, perhaps not all). Hence SAS formats or joining your labels will the appropriate action.
Data never sleeps
Super User
Super User
Posts: 7,405

Re: How do I create labels for more than 400 variables !?

I would agree with @LinusH here.  A normlised table will be far easier to work with.  400 clumns isn't much use to anyone, and reviewers will not look at so much data (I personally rarely even scroll the window in Excel!).

I provide and example of what is meant by transposing and merging below, note its a simple example - if you have mixed number/character you will need to convert one or the other, or have two columns, one for character one for numeric.  But the theory is the same.  Its plain base code, no generation or anything, so easier to maintain.  Also, most of the information you need will be in your data transfer agreement will it not?

/* Simulate data from spreadsheets */
data have;
  id=1; abc=1; def=2; abc1=5; fggh=3; uiui=7; qwer=8; opii=45; output;
  id=2; abc=1; def=2; abc1=5; fggh=3; uiui=7; qwer=8; opii=45; output;
run;
 
data labels;
  length var label $100;
  var="abc"; label="A label for abc"; output;
  var="def"; label="A label for def"; output;
  var="abc1"; label="A label for abc1"; output;
  var="fggh"; label="A label for fggh"; output;
  var="uiui"; label="A label for uiui"; output;
run;

/* Normalise the base data */
proc transpose data=have out=inter;
  by id;
  var _all_;
run;

/* Join lables on */
proc sql;
  create table WANT as
  select  A.*,
          B.LABEL
  from    WORK.INTER A
  left join WORK.LABELS B
  on      A._NAME_=B.VAR;
quit;
Occasional Contributor
Posts: 6

Re: How do I create labels for more than 400 variables !?

Thank you @RW9. Would certainly try this method of transposing & merging too.
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 401 views
  • 7 likes
  • 4 in conversation