DATA Step, Macro, Functions and more

How to update dynamically created variable names

Reply
Contributor
Posts: 65

How to update dynamically created variable names

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.

Super Contributor
Posts: 320

Re: How to update dynamically created variable names

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;
Contributor
Posts: 65

Re: How to update dynamically created variable names

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

vcolumn.jpg

 

Super Contributor
Posts: 320

Re: How to update dynamically created variable names

Libname and memname columns have uppercase values. Try

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

Contributor
Posts: 65

Re: How to update dynamically created variable names

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.

Contributor
Posts: 65

Re: How to update dynamically created variable names

Nice it worked. how Can i rename only for couple of columns in table windows_servers
Super User
Posts: 12,996

Re: How to update dynamically created variable names

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.

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 92 views
  • 1 like
  • 3 in conversation