topic Re: How do I create labels for more than 400 variables !? in SAS Programming

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

pradeepvaranasi — Thu, 03 Mar 2016 07:15:35 GMT

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.

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

LinusH — Thu, 03 Mar 2016 07:18:35 GMT

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.

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

pradeepvaranasi — Thu, 03 Mar 2016 07:24:18 GMT

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 ?

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

Ksharp — Thu, 03 Mar 2016 07:28:00 GMT


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;

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

LinusH — Thu, 03 Mar 2016 07:29:20 GMT

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.

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

pradeepvaranasi — Thu, 03 Mar 2016 07:35:50 GMT

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

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

pradeepvaranasi — Thu, 03 Mar 2016 08:26:28 GMT

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

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

Ksharp — Thu, 03 Mar 2016 09:12:01 GMT

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

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

RW9 — Thu, 03 Mar 2016 09:34:43 GMT

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;

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

pradeepvaranasi — Thu, 03 Mar 2016 09:49:40 GMT

Thank you @RW9. Would certainly try this method of transposing & merging too.