DATA Step, Macro, Functions and more

how to add suffix to a group of selected variables in a dataset

Reply
Contributor
Posts: 64

how to add suffix to a group of selected variables in a dataset

Hi everyone, I'm having a problem adding suffix to a selected group of variables in a data set. I followed the codes at: http://support.sas.com/resources/papers/proceedings09/075-2009.pdf

it did add prefix to a group of variables. But they are not the variables that i was about to add prefix to...

By checking the variable numbers, i was targeting to add prefix to variable number starting from 377 to 403. However, the resulting dataset shows that prefix was added to a group of variables, not as what i selected...

 

I wonder whether it is because the variable list was not sorted before the macro was run. But i have no idea how to sort it.

 

proc contents data=WORK.TRY varnum;
run;

options macrogen mprint mlogic;
%macro rename(lib,dsn, start, end, newname);
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "Before Renaming All Variables";
run;
proc sql noprint;
 select nvar into :num_vars
 from dictionary.tables
 where libname="&LIB" and
 memname="&DSN";
 select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
 from dictionary.columns
 where libname="&LIB" and
 memname="&DSN";
quit;
run;
proc datasets library=&LIB;
 modify &DSN;
 rename
 %do i=&start %to &end;
&&var&i=&newname._&&var&i.
 %end;
 ;
quit;
run;
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "After Renaming All Variables";
run;
%mend rename;
%rename(WORK, TRY, 377, 403, dschrg);

Below is the log

 

MPRINT(RENAME):   proc sql noprint;
MPRINT(RENAME):   select nvar into :num_vars from dictionary.tables where libname="WORK" and memname="TRY";
MPRINT(RENAME):   select distinct(name) into :var1- :
MLOGIC(TRIM):  Beginning execution.
MLOGIC(TRIM):  This macro was compiled from the autocall file C:\Program Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
MLOGIC(LEFT):  Beginning execution.
MLOGIC(LEFT):  This macro was compiled from the autocall file C:\Program Files\SASHome\SASFoundation\9.4\core\sasmacro\left.sas
MLOGIC(LEFT):  Parameter TEXT has value 450
MLOGIC(LEFT):  %LOCAL  I
MLOGIC(LEFT):  %IF condition %length(&text)=0 is FALSE
MLOGIC(LEFT):  %LET (variable name is I)
MLOGIC(VERIFY):  Beginning execution.
MLOGIC(VERIFY):  This macro was compiled from the autocall file C:\Program
      Files\SASHome\SASFoundation\9.4\core\sasmacro\verify.sas
MLOGIC(VERIFY):  Parameter TEXT has value 450
MLOGIC(VERIFY):  Parameter TARGET has value  
MLOGIC(VERIFY):  %LOCAL  I
MLOGIC(VERIFY):  %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
MLOGIC(VERIFY):  %DO loop beginning; index variable I; start value is 1; stop value is 3; by value is 1.
MLOGIC(VERIFY):  %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY):  %GOTO verfnd (label resolves to VERFND).
MLOGIC(VERIFY):  %IF condition &i>%length(&text) is FALSE
MLOGIC(VERIFY):  Ending execution.
MLOGIC(LEFT):  %IF condition &i is TRUE
MLOGIC(LEFT):  Ending execution.
MLOGIC(TRIM):  Parameter VALUE has value 450
MLOGIC(TRIM):  %LOCAL  I
MLOGIC(TRIM):  %DO loop beginning; index variable I; start value is 3; stop value is 1; by value is -1.
MLOGIC(TRIM):  %IF condition %qsubstr(&value,&i,1) ne   is TRUE
MLOGIC(TRIM):  %GOTO TRIMMED (label resolves to TRIMMED).
MLOGIC(TRIM):  %IF condition &i>0 is TRUE
MPRINT(TRIM):  var450
MLOGIC(TRIM):  Ending execution.
MPRINT(RENAME):   from dictionary.columns where libname="WORK" and memname="TRY";
MPRINT(RENAME):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.01 seconds


MPRINT(RENAME):   run;
MPRINT(RENAME):   proc datasets library=WORK;
MPRINT(RENAME):   modify TRY;
MLOGIC(RENAME):  %DO loop beginning; index variable I; start value is 377; stop value is 403; by value is 1.
MLOGIC(RENAME):  %DO loop index variable I is now 378; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 379; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 380; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 381; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 382; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 383; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 384; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 385; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 386; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 387; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 388; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 389; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 390; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 391; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 392; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 393; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 394; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 395; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 396; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 397; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 398; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 399; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 400; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 401; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 402; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 403; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 404; loop will not iterate again.
MPRINT(RENAME):   rename service_from_dt_ip=dschrg_service_from_dt_ip service_thru_dt_fstRehab=dschrg_service_thru_dt_fstRehab
service_thru_dt_ip=dschrg_service_thru_dt_ip sex_ident_cd11=dschrg_sex_ident_cd11 sex_ident_cd12=dschrg_sex_ident_cd12
sex_ident_cd13=dschrg_sex_ident_cd13 sex_ident_cd14=dschrg_sex_ident_cd14 sex_ident_cd_f=dschrg_sex_ident_cd_f
startPeriod_fstRehab=dschrg_startPeriod_fstRehab startPeriod_ip=dschrg_startPeriod_ip
startRemain_fstRehab=dschrg_startRemain_fstRehab startRemain_ip=dschrg_startRemain_ip
start_dt_earAdmin=dschrg_start_dt_earAdmin sum_PTMin10day_grp1=dschrg_sum_PTMin10day_grp1
sum_PTMin10day_grp10=dschrg_sum_PTMin10day_grp10 sum_PTMin10day_grp11=dschrg_sum_PTMin10day_grp11
sum_PTMin10day_grp12=dschrg_sum_PTMin10day_grp12 sum_PTMin10day_grp13=dschrg_sum_PTMin10day_grp13
sum_PTMin10day_grp14=dschrg_sum_PTMin10day_grp14 sum_PTMin10day_grp15=dschrg_sum_PTMin10day_grp15
sum_PTMin10day_grp16=dschrg_sum_PTMin10day_grp16 sum_PTMin10day_grp17=dschrg_sum_PTMin10day_grp17
sum_PTMin10day_grp18=dschrg_sum_PTMin10day_grp18 sum_PTMin10day_grp19=dschrg_sum_PTMin10day_grp19
sum_PTMin10day_grp2=dschrg_sum_PTMin10day_grp2 sum_PTMin10day_grp20=dschrg_sum_PTMin10day_grp20
sum_PTMin10day_grp21=dschrg_sum_PTMin10day_grp21 ;
NOTE: Renaming variable service_from_dt_ip to dschrg_service_from_dt_ip.
NOTE: Renaming variable service_thru_dt_fstRehab to dschrg_service_thru_dt_fstRehab.
NOTE: Renaming variable service_thru_dt_ip to dschrg_service_thru_dt_ip.
NOTE: Renaming variable sex_ident_cd11 to dschrg_sex_ident_cd11.
NOTE: Renaming variable sex_ident_cd12 to dschrg_sex_ident_cd12.
NOTE: Renaming variable sex_ident_cd13 to dschrg_sex_ident_cd13.
NOTE: Renaming variable sex_ident_cd14 to dschrg_sex_ident_cd14.
NOTE: Renaming variable sex_ident_cd_f to dschrg_sex_ident_cd_f.
NOTE: Renaming variable startPeriod_fstRehab to dschrg_startPeriod_fstRehab.
NOTE: Renaming variable startPeriod_ip to dschrg_startPeriod_ip.
NOTE: Renaming variable startRemain_fstRehab to dschrg_startRemain_fstRehab.
NOTE: Renaming variable startRemain_ip to dschrg_startRemain_ip.
NOTE: Renaming variable start_dt_earAdmin to dschrg_start_dt_earAdmin.
NOTE: Renaming variable sum_PTMin10day_grp1 to dschrg_sum_PTMin10day_grp1.
NOTE: Renaming variable sum_PTMin10day_grp10 to dschrg_sum_PTMin10day_grp10.
NOTE: Renaming variable sum_PTMin10day_grp11 to dschrg_sum_PTMin10day_grp11.
NOTE: Renaming variable sum_PTMin10day_grp12 to dschrg_sum_PTMin10day_grp12.
NOTE: Renaming variable sum_PTMin10day_grp13 to dschrg_sum_PTMin10day_grp13.
NOTE: Renaming variable sum_PTMin10day_grp14 to dschrg_sum_PTMin10day_grp14.
NOTE: Renaming variable sum_PTMin10day_grp15 to dschrg_sum_PTMin10day_grp15.
NOTE: Renaming variable sum_PTMin10day_grp16 to dschrg_sum_PTMin10day_grp16.
NOTE: Renaming variable sum_PTMin10day_grp17 to dschrg_sum_PTMin10day_grp17.
NOTE: Renaming variable sum_PTMin10day_grp18 to dschrg_sum_PTMin10day_grp18.
NOTE: Renaming variable sum_PTMin10day_grp19 to dschrg_sum_PTMin10day_grp19.
NOTE: Renaming variable sum_PTMin10day_grp2 to dschrg_sum_PTMin10day_grp2.
NOTE: Renaming variable sum_PTMin10day_grp20 to dschrg_sum_PTMin10day_grp20.
NOTE: Renaming variable sum_PTMin10day_grp21 to dschrg_sum_PTMin10day_grp21.
MPRINT(RENAME):   quit;

NOTE: MODIFY was successful for WORK.TRY.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.17 seconds
      cpu time            0.03 seconds

 

I'll be appreciated if you could share your solution/ thoughts.  In addition, I'm interested to know if there is any convenient way of adding suffix to selected boundary variables yet with nothing in common in their names (eg. eating, bedding, ... etc.). Thank you for your help!

 

 

Super User
Posts: 23,980

Re: how to add suffix to a group of selected variables in a dataset

Posted in reply to Crystal_F

There's nothing in your code that uses VARNUM to filter anything that I see, can you indicate which line you think is filtering out those variables specifically?

 

I would expect to see a WHERE condition in your SQL that would filter on VARNUM.

 


@Crystal_F wrote:

Hi everyone, I'm having a problem adding suffix to a selected group of variables in a data set. I followed the codes at: http://support.sas.com/resources/papers/proceedings09/075-2009.pdf

it did add prefix to a group of variables. But they are not the variables that i was about to add prefix to...

By checking the variable numbers, i was targeting to add prefix to variable number starting from 377 to 403. However, the resulting dataset shows that prefix was added to a group of variables, not as what i selected...

 

I wonder whether it is because the variable list was not sorted before the macro was run. But i have no idea how to sort it.

 

proc contents data=WORK.TRY varnum;
run;

options macrogen mprint mlogic;
%macro rename(lib,dsn, start, end, newname);
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "Before Renaming All Variables";
run;
proc sql noprint;
 select nvar into :num_vars
 from dictionary.tables
 where libname="&LIB" and
 memname="&DSN";
 select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
 from dictionary.columns
 where libname="&LIB" and
 memname="&DSN";
quit;
run;
proc datasets library=&LIB;
 modify &DSN;
 rename
 %do i=&start %to &end;
&&var&i=&newname._&&var&i.
 %end;
 ;
quit;
run;
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "After Renaming All Variables";
run;
%mend rename;
%rename(WORK, TRY, 377, 403, dschrg);

Below is the log

 

MPRINT(RENAME):   proc sql noprint;
MPRINT(RENAME):   select nvar into :num_vars from dictionary.tables where libname="WORK" and memname="TRY";
MPRINT(RENAME):   select distinct(name) into :var1- :
MLOGIC(TRIM):  Beginning execution.
MLOGIC(TRIM):  This macro was compiled from the autocall file C:\Program Files\SASHome\SASFoundation\9.4\core\sasmacro\trim.sas
MLOGIC(LEFT):  Beginning execution.
MLOGIC(LEFT):  This macro was compiled from the autocall file C:\Program Files\SASHome\SASFoundation\9.4\core\sasmacro\left.sas
MLOGIC(LEFT):  Parameter TEXT has value 450
MLOGIC(LEFT):  %LOCAL  I
MLOGIC(LEFT):  %IF condition %length(&text)=0 is FALSE
MLOGIC(LEFT):  %LET (variable name is I)
MLOGIC(VERIFY):  Beginning execution.
MLOGIC(VERIFY):  This macro was compiled from the autocall file C:\Program
      Files\SASHome\SASFoundation\9.4\core\sasmacro\verify.sas
MLOGIC(VERIFY):  Parameter TEXT has value 450
MLOGIC(VERIFY):  Parameter TARGET has value  
MLOGIC(VERIFY):  %LOCAL  I
MLOGIC(VERIFY):  %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
MLOGIC(VERIFY):  %DO loop beginning; index variable I; start value is 1; stop value is 3; by value is 1.
MLOGIC(VERIFY):  %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY):  %GOTO verfnd (label resolves to VERFND).
MLOGIC(VERIFY):  %IF condition &i>%length(&text) is FALSE
MLOGIC(VERIFY):  Ending execution.
MLOGIC(LEFT):  %IF condition &i is TRUE
MLOGIC(LEFT):  Ending execution.
MLOGIC(TRIM):  Parameter VALUE has value 450
MLOGIC(TRIM):  %LOCAL  I
MLOGIC(TRIM):  %DO loop beginning; index variable I; start value is 3; stop value is 1; by value is -1.
MLOGIC(TRIM):  %IF condition %qsubstr(&value,&i,1) ne   is TRUE
MLOGIC(TRIM):  %GOTO TRIMMED (label resolves to TRIMMED).
MLOGIC(TRIM):  %IF condition &i>0 is TRUE
MPRINT(TRIM):  var450
MLOGIC(TRIM):  Ending execution.
MPRINT(RENAME):   from dictionary.columns where libname="WORK" and memname="TRY";
MPRINT(RENAME):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.01 seconds


MPRINT(RENAME):   run;
MPRINT(RENAME):   proc datasets library=WORK;
MPRINT(RENAME):   modify TRY;
MLOGIC(RENAME):  %DO loop beginning; index variable I; start value is 377; stop value is 403; by value is 1.
MLOGIC(RENAME):  %DO loop index variable I is now 378; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 379; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 380; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 381; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 382; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 383; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 384; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 385; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 386; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 387; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 388; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 389; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 390; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 391; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 392; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 393; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 394; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 395; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 396; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 397; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 398; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 399; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 400; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 401; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 402; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 403; loop will iterate again.
MLOGIC(RENAME):  %DO loop index variable I is now 404; loop will not iterate again.
MPRINT(RENAME):   rename service_from_dt_ip=dschrg_service_from_dt_ip service_thru_dt_fstRehab=dschrg_service_thru_dt_fstRehab
service_thru_dt_ip=dschrg_service_thru_dt_ip sex_ident_cd11=dschrg_sex_ident_cd11 sex_ident_cd12=dschrg_sex_ident_cd12
sex_ident_cd13=dschrg_sex_ident_cd13 sex_ident_cd14=dschrg_sex_ident_cd14 sex_ident_cd_f=dschrg_sex_ident_cd_f
startPeriod_fstRehab=dschrg_startPeriod_fstRehab startPeriod_ip=dschrg_startPeriod_ip
startRemain_fstRehab=dschrg_startRemain_fstRehab startRemain_ip=dschrg_startRemain_ip
start_dt_earAdmin=dschrg_start_dt_earAdmin sum_PTMin10day_grp1=dschrg_sum_PTMin10day_grp1
sum_PTMin10day_grp10=dschrg_sum_PTMin10day_grp10 sum_PTMin10day_grp11=dschrg_sum_PTMin10day_grp11
sum_PTMin10day_grp12=dschrg_sum_PTMin10day_grp12 sum_PTMin10day_grp13=dschrg_sum_PTMin10day_grp13
sum_PTMin10day_grp14=dschrg_sum_PTMin10day_grp14 sum_PTMin10day_grp15=dschrg_sum_PTMin10day_grp15
sum_PTMin10day_grp16=dschrg_sum_PTMin10day_grp16 sum_PTMin10day_grp17=dschrg_sum_PTMin10day_grp17
sum_PTMin10day_grp18=dschrg_sum_PTMin10day_grp18 sum_PTMin10day_grp19=dschrg_sum_PTMin10day_grp19
sum_PTMin10day_grp2=dschrg_sum_PTMin10day_grp2 sum_PTMin10day_grp20=dschrg_sum_PTMin10day_grp20
sum_PTMin10day_grp21=dschrg_sum_PTMin10day_grp21 ;
NOTE: Renaming variable service_from_dt_ip to dschrg_service_from_dt_ip.
NOTE: Renaming variable service_thru_dt_fstRehab to dschrg_service_thru_dt_fstRehab.
NOTE: Renaming variable service_thru_dt_ip to dschrg_service_thru_dt_ip.
NOTE: Renaming variable sex_ident_cd11 to dschrg_sex_ident_cd11.
NOTE: Renaming variable sex_ident_cd12 to dschrg_sex_ident_cd12.
NOTE: Renaming variable sex_ident_cd13 to dschrg_sex_ident_cd13.
NOTE: Renaming variable sex_ident_cd14 to dschrg_sex_ident_cd14.
NOTE: Renaming variable sex_ident_cd_f to dschrg_sex_ident_cd_f.
NOTE: Renaming variable startPeriod_fstRehab to dschrg_startPeriod_fstRehab.
NOTE: Renaming variable startPeriod_ip to dschrg_startPeriod_ip.
NOTE: Renaming variable startRemain_fstRehab to dschrg_startRemain_fstRehab.
NOTE: Renaming variable startRemain_ip to dschrg_startRemain_ip.
NOTE: Renaming variable start_dt_earAdmin to dschrg_start_dt_earAdmin.
NOTE: Renaming variable sum_PTMin10day_grp1 to dschrg_sum_PTMin10day_grp1.
NOTE: Renaming variable sum_PTMin10day_grp10 to dschrg_sum_PTMin10day_grp10.
NOTE: Renaming variable sum_PTMin10day_grp11 to dschrg_sum_PTMin10day_grp11.
NOTE: Renaming variable sum_PTMin10day_grp12 to dschrg_sum_PTMin10day_grp12.
NOTE: Renaming variable sum_PTMin10day_grp13 to dschrg_sum_PTMin10day_grp13.
NOTE: Renaming variable sum_PTMin10day_grp14 to dschrg_sum_PTMin10day_grp14.
NOTE: Renaming variable sum_PTMin10day_grp15 to dschrg_sum_PTMin10day_grp15.
NOTE: Renaming variable sum_PTMin10day_grp16 to dschrg_sum_PTMin10day_grp16.
NOTE: Renaming variable sum_PTMin10day_grp17 to dschrg_sum_PTMin10day_grp17.
NOTE: Renaming variable sum_PTMin10day_grp18 to dschrg_sum_PTMin10day_grp18.
NOTE: Renaming variable sum_PTMin10day_grp19 to dschrg_sum_PTMin10day_grp19.
NOTE: Renaming variable sum_PTMin10day_grp2 to dschrg_sum_PTMin10day_grp2.
NOTE: Renaming variable sum_PTMin10day_grp20 to dschrg_sum_PTMin10day_grp20.
NOTE: Renaming variable sum_PTMin10day_grp21 to dschrg_sum_PTMin10day_grp21.
MPRINT(RENAME):   quit;

NOTE: MODIFY was successful for WORK.TRY.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.17 seconds
      cpu time            0.03 seconds

 

I'll be appreciated if you could share your solution/ thoughts.  In addition, I'm interested to know if there is any convenient way of adding suffix to selected boundary variables yet with nothing in common in their names (eg. eating, bedding, ... etc.). Thank you for your help!

 

 


 

Contributor
Posts: 64

Re: how to add suffix to a group of selected variables in a dataset

I used proc content...varnum to track the order number of the target variables, which showed the variables i like to add prefix starting from 377. Thank you.
Super User
Posts: 23,980

Re: how to add suffix to a group of selected variables in a dataset

Posted in reply to Crystal_F

You did, but checking your code, you never actually filtered it anywhere to use those numbers you're passing through, unless I missed it. I searched (CTRL+F) for VARNUM and didn't find it. 
I would expect to see it in the query that creates the variable list and there's nothing there....you're definitely on the right track, it seems you just forgot to add that clause. 

 


@Crystal_F wrote:
I used proc content...varnum to track the order number of the target variables, which showed the variables i like to add prefix starting from 377. Thank you.

 

Contributor
Posts: 64

Re: how to add suffix to a group of selected variables in a dataset

Hi Reeza, thank you for your feedback. But i'm still lost. I thought I used the 'start' and 'end' to restrict rename just for variables with varnum between 377 and 403. Am I wrong about that?
Super User
Posts: 23,980

Re: how to add suffix to a group of selected variables in a dataset

Posted in reply to Crystal_F

Which line in your code do you think does that? I think you've thought about it correctly but forgot to add that condition in to the code.

The answers in the spoiler below, but you should really try and find it yourself first. For example see if start and end are used anywhere in the code!

 

Anyways, some small other comments:

1. You don't need to know the number of macro variables ahead of time, you can use into :var1- in your SQL and it will automatically create the correct number of variables.

2. If you do need the number after, use the automatic macro variable SQLOBS.

3. The proc contents is not needed. 

4. If you can, usually when your variable list is short enough, you can do it all in one SQL step rather than the macro logic later. Just a different way. Your current approach is slightly more versatile because it

 

proc sql noprint;
select distinct age into :age1-
from sashelp.class;
quit;

%let n_ages = &sqlobs;

%put &age1.;
%put &age4.;
%put &&age&sqlobs.;

@Crystal_F wrote:
Hi Reeza, thank you for your feedback. But i'm still lost. I thought I used the 'start' and 'end' to restrict rename just for variables with varnum between 377 and 403. Am I wrong about that?
Spoiler
proc sql noprint;
 select distinct(name) into :var1-
 from dictionary.columns
 where libname="&LIB" and
 memname="&DSN"
AND VARNUM between &start. and &end. /*this is the line you were missing*/

;
quit;



Contributor
Posts: 64

Re: how to add suffix to a group of selected variables in a dataset

Thank you for your help. I'll check on that.
Super User
Posts: 13,913

Re: how to add suffix to a group of selected variables in a dataset

Posted in reply to Crystal_F

May I say that many names like that looks like the data model used might not be optimal.

Variable names like dschrg_sum_PTMin10day_grp1 make me thing you have one column for each "group" of some type. Which would mean that anything that adds a new group would require a lot of code juggling to process the new data. In most cases data that has a variable named Group (or similar) with the value of the group identifier and one record per group value works better as you can use BY group processing or the group variable as a classification in analysis to get results within a group.

 

Also having to create a bunch of Dschrg_ variables would lead me to believe this values have to do with discharge and brings up an immediate suspicion of variables related to "intake" "diagnosis" or "treatment' or similar. Which makes me wonder if for each

dschrg_sum_PTMin10day_grp1 there is a related other grp1 variable that should needs to be linked. At which point those variables are the ones that would have a single name such as dschrg_sum_PTMin10day associated with each group.

 

Sometimes we see such names simply because a data collector is forced or prefers to use a spreadsheet with poor structure for actual analysis and the first step after (or sometimes during reading) is to restructure the data to a normalized data form.

 

 

Contributor
Posts: 64

Re: how to add suffix to a group of selected variables in a dataset

Thank you for sharing your insights. For some reason, i was asked to keep one person one row. sum_PTMin10day_grp1 - sum_PTMin10day_grp37 represent the values in each 10-day interval. But I agree there is some better way of naming variables. The dischrg_ was supposed to be added to the target outcome variables such as dischrg_eating, dischrg_cognitive instead of dschrg_sum_PTMin10day:.
Super User
Posts: 13,913

Re: how to add suffix to a group of selected variables in a dataset

Posted in reply to Crystal_F

@Crystal_F wrote:
Thank you for sharing your insights. For some reason, i was asked to keep one person one row. sum_PTMin10day_grp1 - sum_PTMin10day_grp37 represent the values in each 10-day interval. But I agree there is some better way of naming variables. The dischrg_ was supposed to be added to the target outcome variables such as dischrg_eating, dischrg_cognitive instead of dschrg_sum_PTMin10day:.

When the boss says that's what you do.

Questions to ask the boss though might be:

Are any of these people going to be seen in later data with intervals 38, 39, 40 etc? This means likely the entire bit of code needs to be modified to handle the increased variables, EACH time you get new data.

 

How is this block of variables used later?

   One Example: graphing: interval (or accumulated interval day totals) would typically be used on an X axis to show change over time for a single (or group of) measure(s). With data in this form the first thing before graphing is going to be transforming the data to have an actual X value and a single value which also likely to be the desired format for most regression models involving changes over time.

   Another example: if you want to calculate the value of the changed value from interval to interval you will need 36 different subtractions: grp2- grp1, grp3-grp2. And that code would need to be modified every time another interval is added.  A different data structure gets that in basically a single statement: dif (dschrg_sum_PTMin10day).

 

Data layout should be based on how the data will be used.

Contributor
Posts: 64

Re: how to add suffix to a group of selected variables in a dataset

Thank you for sharing your thoughts again! That's very helpful. I especially like the idea of dif (dschrg_sum_PTMin10day). Thanks!
Ask a Question
Discussion stats
  • 10 replies
  • 231 views
  • 3 likes
  • 3 in conversation