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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
LinusH
Tourmaline | Level 20
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
pradeepvaranasi
Calcite | Level 5
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 ?
Ksharp
Super User

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;
pradeepvaranasi
Calcite | Level 5

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

pradeepvaranasi
Calcite | Level 5

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

Ksharp
Super User

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

LinusH
Tourmaline | Level 20
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
pradeepvaranasi
Calcite | Level 5
Thank you @RW9. Would certainly try this method of transposing & merging too.

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
  • 9 replies
  • 1940 views
  • 8 likes
  • 4 in conversation