topic Re: Grabbing all columns with names that start with a string in a successive pattern in SAS Programming

Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:22:42 GMT

I have no idea how to condense my question into one sentence but what I am trying to do is perform a macro on all columns that name starts with a successive pattern (S1-S100).

So let's say there are 500 columns in which that 100 of them are named S1_Something to S100_Something and the rest are named SOMETHING1_Something to SOMETHING400_Something. I want to perform a macro on all of the columns that start with S1-S100. So what I have so far is kind of an ugly way of doing it but it is

PROC SQL;
    CREATE TABLE ques AS
    SELECT * FROM dictionary.columns
    WHERE memname="HAVE";
QUIT;

PROC TRANSPOSE DATA=ques OUT=quest;
    VAR name;
RUN;

DATA quest;
    SET quest;
    ARRAY cols COL1-COL%EVAL(&sqlobs.-2);
    DO OVER cols;
        IF SCAN(cols, 1, "_") IN ("S1", "S2", "S3") THEN PUT cols;
    END;
RUN;

But instead of outputting the column into the log, I would then concatenate it into a macro variable and then shove that macro variable into a macro that will then loop over all of the strings inside the macro variable and perform the macro on that. So what I have should work however I was wondering if there was an easier way of doing it. Basically having an array of "S1"-"S100" and seeing if it is in that array but I don't believe that is possible. I wouldn't be able to do LIKE S% because it would also grab all of the columns that are like S% which is all of the columns and not just S1_Something - S100_Something.

Go ahead and ask any questions if you are confused on what I want exactly since I'm bad at explaining.

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:02:07 GMT

SASHELP.VCOLUMN -> has a list of all column names you can query.

If you're looping over columns is there a reason an array wouldn't work instead of macros? Macros are useful but there are other methods.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:08:06 GMT

If you look at my code, I got all the way to the end but I don't know how to grab the columns that are in that successive pattern. I grabbed all of the columns and I can subset the columns by making it satisfy it being inside an array of strings that I enter manually but not a list of strings that I can set automatically. And I could possibly just loop over the columns instead of doing a macro but I feel as though the same problem that exists when looping over the columns exists when shoving all of the columns I want inside the macro.

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:12:22 GMT

Have you checked out shortcut lists? If your variables are S1-S1000 you can refer to them as that within your array code at least, this is valid:

array myVars(*) S1-S1000;

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@hubernomiddle wrote:

If you look at my code, I got all the way to the end but I don't know how to grab the columns that are in that successive pattern. I grabbed all of the columns and I can subset the columns by making it satisfy it being inside an array of strings that I enter manually but not a list of strings that I can set automatically. And I could possibly just loop over the columns instead of doing a macro but I feel as though the same problem that exists when looping over the columns exists when shoving all of the columns I want inside the macro.

Re: Grabbing all columns with names that start with a string in a successive pattern

tsap — Mon, 22 Jul 2019 17:13:51 GMT

using this dummy dataset:

DATA WORK.HAVE;
FORMAT 	 ID $1. S1 $1.	S2 $1. S3 $1. L1 $1.L2 $1. L3 $1.;
INFORMAT ID $1. S1 $1.	S2 $1. S3 $1. L1 $1.L2 $1. L3 $1.;
INPUT    ID 	S1 		S2 	   S3 	  L1 	L2	   L3;
INFILE DATALINES DLM='|' DSD;
DATALINES;
1|A|B|C|X|Y|Z
;

I tested this logic:

PROC SQL;
	SELECT DISTINCT name	INTO :ColList separated by ','
	FROM dictionary.columns
    WHERE memname="HAVE"
	AND SUBSTR(name,1,2) IN('S1','S2','S3','S4','S5','S6','S7','S8','S9');
QUIT;

%PUT &=ColList.;

And produced the Macro variable "ColList" with this value printed in the log

%PUT &=ColList.;
COLLIST=S1,S2,S3

Then wherever you want to use the list of columns identified, you can drop that macro variable into place.

Re: Grabbing all columns with names that start with a string in a successive pattern

Tom — Mon, 22 Jul 2019 17:14:01 GMT

You can use the colon prefix to make a variable list of all variables that start with a specific prefix.

array all_cols col: ;

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:16:24 GMT

proc sql noprint;

select name into :var_list separated by " "
from sashelp.vcolumn 
where libname = "WORK"
and memname = "HAVE"
and name like 'S%';

quit;

%put &var_list;

Here's how you can create that variable list in a macro variable, if desired.

@hubernomiddle wrote:

I have no idea how to condense my question into one sentence but what I am trying to do is perform a macro on all columns that name starts with a successive pattern (S1-S100).

So let's say there are 500 columns in which that 100 of them are named S1_Something to S100_Something and the rest are named SOMETHING_Something to SOMETHING_Something. I want to perform a macro on all of the columns that start with S1-S100. So what I have so far is kind of an ugly way of doing it but it is
PROC SQL;
    CREATE TABLE ques AS
    SELECT * FROM dictionary.columns
    WHERE memname="HAVE";
QUIT;

PROC TRANSPOSE DATA=ques OUT=quest;
    VAR name;
RUN;

DATA quest;
    SET quest;
    ARRAY cols COL1-COL%EVAL(&sqlobs.-2);
    DO OVER cols;
        IF SCAN(cols, 1, "_") IN ("S1", "S2", "S3") THEN PUT cols;
    END;
RUN;
But instead of outputting the column into the log, I would then concatenate it into a macro variable and then shove that macro variable into a macro that will then loop over all of the strings inside the macro variable and perform the macro on that. So what I have should work however I was wondering if there was an easier way of doing it. Basically having an array of "S1"-"S100" and seeing if it is in that array but I don't believe that is possible. I wouldn't be able to do LIKE S% because it would also grab all of the columns that are like S% which is all of the columns and not just S1_Something - S100_Something.

Go ahead and ask any questions if you are confused on what I want exactly since I'm bad at explaining.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:18:22 GMT

How do you make a string of successive S1-S1000 to then compare it to the string of the column name or how do I get a SCAN(colname, 1, "_") to equal the successive variables S1-S1000.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:20:19 GMT

I see that you can do that but I'm trying to make it so I don't have to specify each individual cell. Not sure if that's possible or not. So basically a "S1"-"S1000" type deal.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:21:10 GMT

I realize you can do that but similar to the "LIKE" problem, it will grab all of the columns that start with "SOMETHING" and not just the ones that start with "S"

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:22:22 GMT

Like the example that I had given, that code will grab all of the columns that start with "S" which would grab the columns SOMETHING1_something-SOMETHING400_something and notj ust S1_something-S100_something.

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:22:31 GMT

@hubernomiddle wrote:

How do you make a string of successive S1-S1000 to then compare it to the string of the column name or how do I get a SCAN(colname, 1, "_") to equal the successive variables S1-S1000.

I don't know what that means. Show some example input data with 5 columns and explain what you want as output, or include the example output and someone can likely provide several different methods to accomplish it. There's clearly people here who are more than eager to help if you provide enough details 🙂

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:26:38 GMT

@hubernomiddle wrote:

Like the example that I had given, that code will grab all of the columns that start with "S" which would grab the columns SOMETHING1_something-SOMETHING400_something and notj ust S1_something-S100_something.

You can make like more complex though, even use REGEX if you want to make it super complicated. You're finding a lot of reasons why our solutions won't work for you. I really suggest explaining what you're doing in more detail at this point.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Mon, 22 Jul 2019 17:27:57 GMT

I know I'm terrible at explaining.

So let's say I have 500 columns. 100 which is named S1_something, S2_something, S3_something... , S100_something and and SOMETHING1_something, SOMETHING2_something, SOMETHING3_something,... SOMETHING400_something. I want to grab just the 100 columns that start with S1-S100 and not the ones that start with SOMETHING1-SOMETHING400. The reason why I want to grab those columns is that I want to then perform a macro, or loop through those columns and perform a function. Or another way of looking at it is that I want to only KEEP those columns, S1- S100, and DROP the rest because if I do, then I can just loop over all of the columns of the remaining data set and then merge it with the original. Not sure if that clears anything up.

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 17:29:20 GMT

Read this:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Re: Grabbing all columns with names that start with a string in a successive pattern

tsap — Mon, 22 Jul 2019 17:50:37 GMT

you aren't specifying each cell in my logic. The first two characters of the columns can be S1 through S9... so any number S999, S1000, S845... they would all get picked up using my logic without any changes to the code whatsoever.

Re: Grabbing all columns with names that start with a string in a successive pattern

hubernomiddle — Tue, 23 Jul 2019 15:54:49 GMT

Okay so I got closer to what I want by reading the link you gave me with

PROC SQL;
    SELECT name || "=" || CATS(SUBSTR(name, 1, 27), "_temp") INTO :rename_list SEPARATED BY " "
    FROM dictionary.columns
    WHERE libname="WORK"
    AND memname="HAVE";
QUIT;

PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY HAVE;
    RENAME &rename_list.;
QUIT;

PROC SQL;
    SELECT name || "=" || SCAN(name, 1, "_") INTO :rename_list SEPARATED BY " "
    FROM dictionary.columns
    WHERE libname="WORK"
    AND memname="HAVE";
QUIT;

PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY HAVE;
    RENAME &rename_list.;
QUIT;

And the only thing that doesn't work now is the macro. I successfully renamed all of the variables to S1-S7 P1-P9, etc. instead of having them be S1_Something-S7_Something

ANOTHER WAY OF DOING IT

https://communities.sas.com/t5/SAS-Programming/Grabbing-all-columns-with-names-that-start-with-a-string-in-a/m-p/575470/highlight/true#M162797

https://communities.sas.com/t5/SAS-Programming/Grabbing-all-columns-with-names-that-start-with-a-string-in-a/m-p/575620/highlight/true#M162868

Re: Grabbing all columns with names that start with a string in a successive pattern

Reeza — Mon, 22 Jul 2019 18:04:13 GMT

Is that what you're trying to do? Rename variables? You should be using PROC DATASETS then instead of a data step. You can also rename series quite easily, but I do not recommend using a suffix because you cannot easily reference them later on.

So renaming S1-S7 to S_S1-S_S7 would be:

This would work.

RENAME S1-S7 = S_S1-S_S7;

Re: Grabbing all columns with names that start with a string in a successive pattern

Tom — Mon, 22 Jul 2019 18:14:27 GMT

@hubernomiddle wrote:

I know I'm terrible at explaining.

So let's say I have 500 columns. 100 which is named S1_something, S2_something, S3_something... , S100_something and and SOMETHING1_something, SOMETHING2_something, SOMETHING3_something,... SOMETHING400_something. I want to grab just the 100 columns that start with S1-S100 and not the ones that start with SOMETHING1-SOMETHING400. The reason why I want to grab those columns is that I want to then perform a macro, or loop through those columns and perform a function. Or another way of looking at it is that I want to only KEEP those columns, S1- S100, and DROP the rest because if I do, then I can just loop over all of the columns of the remaining data set and then merge it with the original. Not sure if that clears anything up.

So your issue is that someone or something has added suffixes to the variables of interest and you want to rename those variables?

Or do you just want to identify those variables?

So is your pattern of interest variable names like Sdddd_XXXX where dddd can be a number between 1 and 9999 and XXXX can be any character? So take the list of names that actually exist and apply some logic to find the one you are interested in. You can then either rename them or just use them. Might help to put the list into a macro variable (or two).

proc contents data=have noprint out=contents; run;
data vars_of_interest;
  set contents;
  name=upcase(name);
  if name=:'S' and index(name,'_');
  if 1 <= input(substr(scan(name,1,'_',2),?32.) <= 9999;
  new_name = scan(name,1,'_');
run;
proc sql noprint;
  select name, new_name, catx('=',name,new_name)
    into :old_names separated by ' '
       , :new_names separated by ' '
       , :rename separated by ' '
  from vars_of_interest
  ;
quit;

Then you can use the macro variable to help you write the code to use your dataset.

data want;
  set have;
  array svars &old_names ;
  ....
  rename &rename ;
run;

Re: Grabbing all columns with names that start with a string in a successive pattern

Shmuel — Mon, 22 Jul 2019 19:05:34 GMT

Suppose you have three groups of variables:

1) V_prefixA_1_suffixA - V_prefixA_100_suffixA

2) V_prefixA_1_suffixB - V_prefixA_100_suffixB

3) V_prefixA_1 - V_prefixA_100

you want to grab only one group.

- using V_prefixA_: will grab all the three groups !

- A list defined by: Array V_prefixA_1 - V_prefixA_100; will grab group 3 only,
but you can't use that method to grab group 1 or group 2.

In that case I suggest to do a massive rename of variables:

- rename group 1 to V_prefixA_suffixA_1 - V_prefixA_suffixA_100 and define list by V_prefixA_suffixA_:

- rename group 2 to V_prefixA_suffixB_1 - V_prefixA_suffixB_100 and define list by V_prefixA_suffixB_:

If your case is similar to above you can do the massive rename by:

proc sql;
      create table var_names as select
      name from dictionary.columns
      where memname='HAVE';
quit;

filename ren temp;
data _null_;
  set var_names;
        length a_line $80;
        chk1 = 'V_prefixA_';
        chk2 = '_suffixA';
        chk3 = '_suffixB';

        ix1 = index(name,trim(chk1));
        ix2 = index(name,trim(chk2));
        ix3 = index(name,trim(chk3));
        if ix1=1 then do;
           if ix2 > 1 then new_name = cats(ch1,chk2,scan(name,3,'_')); else
           if ix3 > 1 then new_name = cats(ch1,chk3,scan(name,3,'_')); 
           file ren;
           a_line = cats(name,' = ',new_name);
           put a_line;
        end;
run;

proc datasets lib=<library having data HAVE >;
     modify have;
     %include ren;
quit; run;

you should adapt the CHK# constants to check according to your specific variable names.

The SCAN function should grab the sequence number of the variable in the list.

Pay attention - using proc datasets - modify statement will modify your original HAVE dataset.

You can rename the variables creating new dataset by:

data new;
 set have;
       %include ren;
run;