topic How to update dynamically created variable names in SAS Programming

How to update dynamically created variable names

radha009 — Fri, 16 Mar 2018 14:54:47 GMT

I have an dataset with column names as (id, server, windows_123,test_345,learn_678,.....etc) for current month. in next month i will have an dataset with different column names as (id, server, windows_abc,test_def,learn_ghi,.....etc). How can i rename the column names with like option and use the column names in my generating a report. Can any one suggest an option.

Re: How to update dynamically created variable names

gamotte — Fri, 16 Mar 2018 15:09:04 GMT

Hello,

The rule to match columns from the second dataset to the first one is unclear.

Assuming that columns names have a second part separated by an underscore,

the following program renames columns by keeping only the part preceeding the

underscore.

data have;
    input windows_123 test_456;
    cards;
1 2
3 4
    ;
run;

data _NULL_;
    set sashelp.vcolumn end=eof;
    where libname="WORK" and memname="HAVE";

    if _N_=1 then do;
        call execute('data want; set have; rename');
    end;

    call execute(cats(NAME,'=',scan(NAME,1,"_")));

    if eof then do;
        call execute('; run;');
    end;
run;

Re: How to update dynamically created variable names

ballardw — Fri, 16 Mar 2018 15:28:42 GMT

Changing variable names in this fashion is an indication of poor data structuring and/or method of bringing the data into SAS. You would be better off to ensure that the variable names remain the same at import and add a variable to indicate the date the data represents.

If the files are text, such as CSV or other delimited file or fixed column, then a data step would easily accomplish this assuming the file layout does not change.

Re: How to update dynamically created variable names

radha009 — Fri, 16 Mar 2018 16:42:34 GMT

Thank you for quick respose.

When i ran i got the 0 observations.Also at rename how to provide without column names. my data cloumn names will different (means it will have contain word) each time i run the script.

1598 data _NULL_;
1599      set sashelp.vcolumn end=eof;
1600      where libname="Patch" and memname="WINDOWS_SERVERS";
1601
1602      if _N_=1 then do;
1603          call execute('data Patch.Windows_Servers1; set Patch.Windows_Servers; rename');
1604      end;
1605
1606      call execute(cats(NAME,'=',scan(NAME,1,"_")));
1607
1608      if eof then do;
1609          call execute('; run;');
1610      end;
1611 run;

NOTE: There were 0 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='Patch') and (memname='WINDOWS_SERVERS');
NOTE: DATA statement used (Total process time):
      real time           3.67 seconds
      cpu time            3.13 seconds

Re: How to update dynamically created variable names

gamotte — Fri, 16 Mar 2018 16:52:03 GMT

Libname and memname columns have uppercase values. Try

where libname="PATCH" and memname="WINDOWS_SERVERS";

Re: How to update dynamically created variable names

radha009 — Fri, 16 Mar 2018 17:00:24 GMT

Nice it worked. how Can i rename only for couple of columns in table windows_servers

Re: How to update dynamically created variable names

radha009 — Thu, 12 Apr 2018 14:16:47 GMT

The variable names are changing as per the above code when the variable is SQL_test_TXT4324324 to SQL. But, I have the two variables with same starting name as SQL_test_TXT4324324 and SQL_test2_TXT432677. Can you help me how to rename similar start name variables.