BookmarkSubscribeRSS Feed
radha009
Quartz | Level 8

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.

6 REPLIES 6
gamotte
Rhodochrosite | Level 12

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;
radha009
Quartz | Level 8

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

 

gamotte
Rhodochrosite | Level 12

Libname and memname columns have uppercase values. Try

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

radha009
Quartz | Level 8

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.

radha009
Quartz | Level 8
Nice it worked. how Can i rename only for couple of columns in table windows_servers
ballardw
Super User

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.

 

 

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
  • 6 replies
  • 817 views
  • 1 like
  • 3 in conversation