Help understanding code

[cypher85]

Hi Guys,

Everyone here is so helpful it not even funny. You guys are godsends for the help you provide.

Earlier in the week I asked for some help regarding renaming variables in one dataset, based on values found in a master dataset. I have been able to modify the code so that it works for me. But, I fundementally do not understand the code and what all the elements that make it up are doing. If someone could please help me out and outline what each line of the code is doing and it's purpose. I can modify the code so that it works for me, but I don't fundementally understand it. And I would like to be able to learn more about Proc SQL, and better understanding this complicated code would go a long way. Thank you in advance for anyone that would be willing to walk a proc sql noob through this code.

proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

create table temp as

    select * from renames

             where original in (&names);

  select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

quit;

%put &rename;

proc datasets nolist lib=work;

  modify b ;

    rename &rename;

  run;

quit;

data &sec;

set b;

run;

%MEND mshort_all;

options mprint;

%mshort_all(work.list2)

[art297]

Can't walk you through it until you add the missing line(s).  Since it is creating and running a macro, there has to be some line(s) missing at the beginning.

[cypher85]

Here is my entire code. Sorry for not including the top portion, I didn't include it because I understand what's going on with it. But, it makes sense that anyone willing to help me with the bottom portion needs that portion too.

%macro mshort_all(sec);

data renames ;

length original newname $32 ;

original = 'B9_' ; newname = 'submitdate' ; output ;

original = 'B9_' ; newname = 'cannotfillout' ; output ;

original = 'B9_' ; newname = 'otherspecify' ; output ;

original = 'B9_28' ; newname = 'dateofeval' ; output ;

original = 'B9_29' ; newname = 'position' ; output ;

original = 'B9_30' ; newname = 'otherspecify__1' ; output ;

original = 'B9_31' ; newname = 'attending' ; output ;

original = 'B9_1' ; newname = 'weight' ; output ;

original = 'B9_2' ; newname = 'breastpain' ; output ;

original = 'B9_3' ; newname = 'karnofsky_kps' ; output ;

original = 'B9_4_2' ; newname = 'areas_scar' ; output ;

original = 'B9_4_3' ; newname = 'areas_fold1' ; output ;

original = 'B9_4_4' ; newname = 'areas_fold2' ; output ;

original = 'B9_4_5' ; newname = 'areas_other' ; output ;

original = 'B9_4_1' ; newname = 'areas_na' ; output ;

original = 'B9_4_6' ; newname = 'moistelsewhere' ; output ;

original = 'B9_5_2' ; newname = 'areas2_scar' ; output ;

original = 'B9_5_3' ; newname = 'areas2_fold1' ; output ;

original = 'B9_5_4' ; newname = 'areas2_fold2' ; output ;

original = 'B9_5_5' ; newname = 'areas2_other' ; output ;

original = 'B9_5_1' ; newname = 'areas2_na' ; output ;

original = 'B9_5_6' ; newname = 'dryelsewhere' ; output ;

original = 'B9_6_1' ; newname = 'breasterythema' ; output ;

original = 'B9_6_2' ; newname = 'locationerythema_scar' ; output ;

original = 'B9_6_3' ; newname = 'locationerythema_fold1' ; output ;

original = 'B9_6_4' ; newname = 'locationerythema_fold2' ; output ;

original = 'B9_6_5' ; newname = 'locationerythema_other' ; output ;

original = 'B9_7_1' ; newname = 'rectreatments_calendula' ; output ;

original = 'B9_7_2' ; newname = 'rectreatments_aquaphor' ; output ;

original = 'B9_7_3' ; newname = 'rectreatments_alra' ; output ;

original = 'B9_7_4' ; newname = 'rectreatments_miaderm' ; output ;

original = 'B9_7_5' ; newname = 'rectreatments_biafene' ; output ;

original = 'B9_7_6' ; newname = 'rectreatments_silvadene' ; output ;

original = 'B9_7_7' ; newname = 'rectreatments_aloe' ; output ;

original = 'B9_7_8' ; newname = 'rectreatments_corn' ; output ;

run ;

data &sec;

set &sec;

run;

data b;

set &sec;

run;

proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

create table temp as

    select * from renames

             where original in (&names);

  select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

quit;

%put &rename;

proc datasets nolist lib=work;

  modify b ;

    rename &rename;

  run;

quit;

data &sec;

set b;

run;

%MEND mshort_all;

options mprint;

%mshort_all(work.list2)

[kuridisanjeev]

What you want to do exactly with code???

is there any error getting in this code???

[cypher85]

There is no error, it runs perfectly fine. However, I did not write it, I modified the code that was given on here by a few nice individuals, so that it would work for my specific datasets. But, it's the old "give a man a fish/teach a man to fish" proverb. I am now asking for assistance in understanding what the specific lines of the code are accomplishing. I simply do not really understand it. Specifically this part, and any help outling each step of the code and what it is doing would be greatly, greatly appreciated.

proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

create table temp as

    select * from renames

             where original in (&names);

  select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

quit;

%put &rename;

proc datasets nolist lib=work;

  modify b ;

    rename &rename;

  run;

quit;

[kuridisanjeev]

I am not sure whether you understand this  or not.

but i tried my level best....

• proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

In above code you are storing all name column values  into a macro variable with "," separator..

(the names will stores in this macro variable like this.."aaa,bbb,ccc,ddd".)

• create table temp as

    select * from renames

             where original in (&names);

In above you are creating a temp dataset and here you are putting a condition where original values in

names(name macro variable having all the name value in B data set).

• select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

Here you are creating another macro variable with name called "rename" and you are storing "original=newname" value into that macro variable.

(the value will stores like this.."aaa=aaa1,bbb=bbb1...so on"

• proc datasets nolist lib=work;

       modify b ;

        rename &rename;

        run;

       quit;

Here you are changing all the b data set name by using "rename" macro.

Thank you..

Sanjeev

[Linlin]

this part is incorect

(the names will stores in this macro variable like this.."aaa,bbb,ccc,ddd".)

should be:.."aaa","bbb","ccc","ddd"

[kuridisanjeev]

Dear Linlin..

For identifying purpose, that macro variable value showed in double quotes,

actual values are stored like this..

aaa,bbb,ccc

it won't include double quotes

[Linlin]

Hi,

There are quotes because I used "select quote(trim(name)) ".

I provided the code to the OP

data renames ;

  input ( original newname) (:$32.);

cards;

b10 FirstDose

b11 SecondDose

b12 third

run;

data b;

b10 =3;

b11=8;

b13=9;

run;

proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

create table temp as

    select * from renames

             where original in (&names);

   select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

quit;

%put &names &rename;

proc datasets nolist lib=work;

  modify b ;

    rename &rename;

  run;

quit;

[kuridisanjeev]

Let me know you got the point or not..

if NO i'll try in other way...

Regards,,

Sanjeev

[art297]

Again, it will waste the least time if you ensure that you provide the "exact" code you are running.  Like Linlin suggested, the code you just posted probably isn't what you actually ran.

E.g., three of the early lines look like they will be attempting to assign three different names for the same variable.  i.e.:

original = 'B9_' ; newname = 'submitdate' ; output ;

original = 'B9_' ; newname = 'cannotfillout' ; output ;

original = 'B9_' ; newname = 'otherspecify' ; output ;

Additionally, there appears to be some code that doesn't serve any purpose.  Specifically,

data &sec;

set &sec;

run;

doesn't appear to do anything but require an extra pass through your data with no result.

However, I think you may have already been provided with enough explanation.

[cypher85]

"Again, it will waste the least time if you ensure that you provide the "exact" code you are running.  Like Linlin suggested, the code you just posted probably isn't what you actually ran."

The fact that people who actually know what they are doing question if I even ran the code provided (which I did) just highlights my inherent shortcomings in sas programming.

no that is the actual code I ran. Though we do have some problems with naming conventions where the full name is missing, and some things that need to be cleaned up. So I know it looks weird. But, that is the actual code that I am using and that I ran. Though right now everything is at a testing stage. There is still quite a bit of cleaning that needs to happen not only with my code. But, with the actual data that I will be using.

And the redundent code you mentioned I actually did write and had in my program. Only points to my inefficiency at programming. I just wanted to write in a check that made sure I was working with the actual data and that it was set properly.

data &sec;

set &sec;

run;

I guess it is redundent, however as I mentioned I am working trying to learn more sas and how to simplify and make my code as efficient as it can be. So I know that my code isn't up to snuff for the people on here. That's why I initially only provided the part of the code that was really confusing to me. Then only when asked did I provide the rest of my code. 

But, thanks so much to all of you kind people that are willing to help out.

[art297]

Okay, sorry if I sounded condescending earlier .. I didn't mean to!

The code is difficult for someone new to SAS to understand, because it is using the concept of a SAS macro (among other things) that, honestly, are difficult concepts to grasp.

The code, as written, will ONLY work if the variables names are uppercase.  That, of course, could easily lead to a problem.  The 3 variables in the beginning of renames should be either be removed or replaced with the variables that they supposedly represent.

I actually preferred KSharp's originally suggested code and will explain a slight modification of it below:

The first part is just running your original datastep that created the file called renames.  There is nothing in it that requires a SAS macro, thus I just ran it as a separate datastep:

data renames ;

length original newname $32 ;

original = 'B9_' ; newname = 'submitdate' ; output ;

original = 'B9_' ; newname = 'cannotfillout' ; output ;

original = 'B9_' ; newname = 'otherspecify' ; output ;

original = 'B9_28' ; newname = 'dateofeval' ; output ;

original = 'B9_29' ; newname = 'position' ; output ;

original = 'B9_30' ; newname = 'otherspecify__1' ; output ;

original = 'B9_31' ; newname = 'attending' ; output ;

original = 'B9_1' ; newname = 'weight' ; output ;

original = 'B9_2' ; newname = 'breastpain' ; output ;

original = 'B9_3' ; newname = 'karnofsky_kps' ; output ;

original = 'B9_4_2' ; newname = 'areas_scar' ; output ;

original = 'B9_4_3' ; newname = 'areas_fold1' ; output ;

original = 'B9_4_4' ; newname = 'areas_fold2' ; output ;

original = 'B9_4_5' ; newname = 'areas_other' ; output ;

original = 'B9_4_1' ; newname = 'areas_na' ; output ;

original = 'B9_4_6' ; newname = 'moistelsewhere' ; output ;

original = 'B9_5_2' ; newname = 'areas2_scar' ; output ;

original = 'B9_5_3' ; newname = 'areas2_fold1' ; output ;

original = 'B9_5_4' ; newname = 'areas2_fold2' ; output ;

original = 'B9_5_5' ; newname = 'areas2_other' ; output ;

original = 'B9_5_1' ; newname = 'areas2_na' ; output ;

original = 'B9_5_6' ; newname = 'dryelsewhere' ; output ;

original = 'B9_6_1' ; newname = 'breasterythema' ; output ;

original = 'B9_6_2' ; newname = 'locationerythema_scar' ; output ;

original = 'B9_6_3' ; newname = 'locationerythema_fold1' ; output ;

original = 'B9_6_4' ; newname = 'locationerythema_fold2' ; output ;

original = 'B9_6_5' ; newname = 'locationerythema_other' ; output ;

original = 'B9_7_1' ; newname = 'rectreatments_calendula' ; output ;

original = 'B9_7_2' ; newname = 'rectreatments_aquaphor' ; output ;

original = 'B9_7_3' ; newname = 'rectreatments_alra' ; output ;

original = 'B9_7_4' ; newname = 'rectreatments_miaderm' ; output ;

original = 'B9_7_5' ; newname = 'rectreatments_biafene' ; output ;

original = 'B9_7_6' ; newname = 'rectreatments_silvadene' ; output ;

original = 'B9_7_7' ; newname = 'rectreatments_aloe' ; output ;

original = 'B9_7_8' ; newname = 'rectreatments_corn' ; output ;

run ;

/* Next, I used a datastep to create a sample dataset, like the one that was modified in the original code*/

data list2;

  input B9_1 B9_4_2;

  cards;

1 2

3 4

;

/* Then I created one macro variable, called &thefile.  It is the only statement that you would have to change

in order to run the code on a different file */

%let thefile=LIST2;

/* Then I used proc sql to create a dataset, called applicable_renames that will contain the names

of all of the variables that exist in the file referred to by &thefile.  The file is both created and sorted

in the proc sql call*/

proc sql noprint;

  create table applicable_renames as

    select name as original

      from dictionary.columns

        where libname='WORK' AND

              memname="&thefile."

          order by original

  ;

quit;

/* Then I sorted the file renames in order of the variable original*/

proc sort data=renames;

  by original;

run;

/* Then I merged the two files, applicable_renames and renames, so that applicable_renames would only

include those variables that exist in the file referred to by &thefile. and had a recode identified in renames*/

data applicable_renames;

  merge applicable_renames (in=a) renames (in=b);

  by original;

  if a and b;

run;

/* Now, using KSharp's proposed method, proc datasets only has to rename the variables that really

have to be renamed.  His code works by using call execute to create a program that will run, by itself,

as soon as SAS is finished running the data_null step*/

data _null_;

set applicable_renames end=last;

if _n_ eq 1 then call execute(

  "proc datasets nolist lib=work;

     modify &thefile.;

     rename");

call execute(catx('=',original,newname));

if last then call execute(';quit;');

run;

/* EXTRA CREDIT:  I liked KSharp's approach because I really don't think you need to rename the variables

but, rather, just need to assign labels to them.  If that is true, then instead of the previous data_null step,

all you would have to change in the above code is to run the following data_null datastep instead:*/

data _null_;

set applicable_renames end=last;

if _n_ eq 1 then call execute(

  "proc datasets nolist lib=work;

     modify &thefile.;

     label");

call execute(catx('=',original,newname));

if last then call execute(';quit;');

run;