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.
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;
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;
Thank you LinusH & Ksharp. Working on Ksharp's solution.
It worked. Thank you. Could you please brief me a little theortical explanation on your solution ?
Once you saw the sas code generated by SAS in LOG, You will know what I am trying to do .
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.