topic Re: List of variables based on suffix (not prefix) in New SAS User

List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 16:49:18 GMT

Is there a way to reference a list of variables based on a common suffix?

I simply want to reference a list in a KEEP statement without having to include every single variable. The variables are not continuous with each other in the data set. Below are some sample variables, but I have to do this for many many variables so a list would be preferred. Thanks.

Example:

A09CGTDY

B09CGTDY

C09CGTDY

D09CGTDY

E09CGTDY

F09CGTDY

G09CGTDY

H09CGTDY

I09CGTDY

DATA new_dataset;
   SET old_dataset;
   KEEP A09CGTDY B09CGTDY C09CGTDY D09CGTDY E09CGTDY F09CGTDY G09CGTDY H09CGTDY I09CGTDY;
RUN;

Re: List of variables based on suffix (not prefix)

PaigeMiller — Thu, 27 Jul 2023 17:22:32 GMT

proc sql noprint;
    select name into :colnames separated by ' ' 
    from dictionary.columns
    where upcase(substr(name,4)) eq 'CGTDY' /* Your suffix goes here */
        and libname='WORK' /* Whatever libname is needed goes here, upper case */
        and memname='OLD_DATASET' /* Your data set name goes here, upper case */
;
quit;

data new_dataset;
    set old_dataset;
    keep &colnames;
run;

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 17:23:03 GMT

Got 2 errors (switched the variable list on you...A02SMOKE, B02SMOKE, C02SMOKE).

Any advice?

See log below:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc sql noprint;

74 select name into :colnames separated by ' '

75 from dictionary.columns

76 where substr(name,4) eq 'SMOKE' /* Your suffix goes here */

77 and libname='CARDIA' /* Whatever libname is needed goes here, upper case */

78 and memname='CARDIA.C1260REQ06_29_2023' /* Your data set name goes here, upper case */

79 ;

NOTE: No rows were selected.

80 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 5342.34k

OS Memory 35752.00k

Timestamp 07/27/2023 05:20:00 PM

Step Count 74 Switch Count 0

Page Faults 0

Page Reclaims 56

Page Swaps 0

Voluntary Context Switches 5

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

82 data new_dataset;

83 set CARDIA.C1260REQ06_29_2023;

NOTE: Data file CARDIA.C1260REQ06_29_2023.DATA is in a format that is native to another host, or the file encoding does not match

the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might

reduce performance.

84 keep &colnames ID;

200

WARNING: Apparent symbolic reference COLNAMES not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored.

85 run;

WARNING: The variable colnames in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.NEW_DATASET may be incomplete. When this step was stopped there were 0 observations and 1 variables.

WARNING: Data set WORK.NEW_DATASET was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.06 seconds

user cpu time 0.06 seconds

system cpu time 0.01 seconds

memory 7987.37k

OS Memory 37984.00k

Timestamp 07/27/2023 05:20:00 PM

Step Count 75 Switch Count 0

Page Faults 0

Page Reclaims 1950

Page Swaps 0

Voluntary Context Switches 5

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

87 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: List of variables based on suffix (not prefix)

ballardw — Thu, 27 Jul 2023 17:25:07 GMT

Going forward I would suggest some planning when it comes to variable names. If they have multiple relationships in the names, i.e. you have A01XXX , A02XXX, A03XXX (where XXX is just some other string of related text) then perhaps an ideal name structure to to use for all purposes but XXXA01 XXXA02 XXXA03 would let you use lists like XXXA: to get all the XXXA variables, or XXXA01-XXXA15 (assuming you have all those ending in A02 through A14 and more such as up to A100).

However if you have A01XXX and A01YYY and A01ZZZ that you also want to use lists like A: or A01: your going to have to deal with long lists. But this might be indicative that the data structure could be improved and perhaps the text of XXX, YYY and ZZZ should be the value of a variable.

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 17:28:31 GMT

Unfortunately I have no say in the naming of the variables. It's an analysis of previously collected data w/ 4962 variables.

Re: List of variables based on suffix (not prefix)

PaigeMiller — Thu, 27 Jul 2023 17:31:51 GMT

NOTE: No rows were selected.

One possible cause is that the data set name cannot have a dot in it in the MEMNAME column; the libname and dot is not part of the data set name for the purposes of this SQL search.

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 17:36:15 GMT

Yep, that fixed it! Thanks.

What would be the best way to do this for multiple lists? Would I do a separate proc sql for each list?

Thanks again.

Re: List of variables based on suffix (not prefix)

PaigeMiller — Thu, 27 Jul 2023 17:45:07 GMT

@_maldini_ wrote:

What would be the best way to do this for multiple lists? Would I do a separate proc sql for each list?

Explain this further. Give details. Give examples. Don't make us guess what you mean.

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 18:03:22 GMT

Apologies. I want to subset an enormous dataset w/ 4962 variables using a KEEP statement. The data are derived from a longitudinal study w/ multiple time points. Variables that begin with "A" represent time point 1. Variables that begin with "B" represent time point 2. Etc.

The list of variables I used in my original question was 1 variable measured at multiple time points (A09CGTDY to I09CGTDY). I want to include all time points for this variable in the subset.

I want to use the KEEP statement to reference multiple lists. This list is just for one variable. I want to do this for 25+ variables.

Would I create a separate proc sql for each variable w/ its individual suffix and character count?

Sorry for not being more clear in the beginning.

Re: List of variables based on suffix (not prefix)

PaigeMiller — Thu, 27 Jul 2023 18:09:31 GMT

I want to use the KEEP statement to reference multiple lists. This list is just for one variable. I want to do this for 25+ variables.

You said very similar things in an earlier post, and I asked for examples. I don't see examples of what you mean.

Re: List of variables based on suffix (not prefix)

Tom — Thu, 27 Jul 2023 18:24:01 GMT

Create a dataset with the list of variables and then you can create categories for them.

Let's keep it simple and just use PROC CONTENTS to get the variable names.

proc contents data=mylib.mydata out=contents noprint; run;

Now you can use the name to figure out the groups. Perhaps you want to pull out a 1 character prefix and a 3 character suffix?

data groups;
  set contents;
  prefix = substr(name,1,1);
  suffix= substr(name,length(name)-2);
  middle = substrn(name,2,length(name)-4);
run;

Now you can query the list more easily;

proc sql noprint;
select nliteral(name) 
  into :namelist separated by ' '
  from groups
  where prefix in ('A','C')
    and suffix in ('TDY')
;
quit;

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 19:35:22 GMT

Yikes, you're asking for a lot more questions w/ that idea!

Before I try to adapt that code to play with it, what output will querying the list using PROC SQL produce?

Re: List of variables based on suffix (not prefix)

_maldini_ — Thu, 27 Jul 2023 19:46:23 GMT

Here is what my primitive SAS brain was thinking:

&colnames is a macro variable for the list of variables w/ a suffix of SMOKE.

&colnames2 is a macro variable for the list of variables w/ a suffix of TEST, etc.

I'm sure there are better ways of doing this, but I was asking about writing a separate proc sql for each list of variables (&colnames2=age, &colnames3=physical activity, &colnames4=alcohol use, etc.).

proc sql noprint;
    select name into :colnames separated by ' ' 
    from dictionary.columns
    where substr(name,4) eq 'SMOKE' /* Your suffix goes here */
        and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
        and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
;
quit;


proc sql noprint;
    select name into :colnames2 separated by ' ' 
    from dictionary.columns
    where substr(name,4) eq 'TEST' /* Your suffix goes here */
        and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
        and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
;
quit;

data new_dataset;
    set CARDIA.C1260REQ06_29_2023;
    keep &colnames &colnames2 ID;
run;

Re: List of variables based on suffix (not prefix)

Reeza — Thu, 27 Jul 2023 19:54:51 GMT

This method will work, just make sure that the substr() that limits to the characters after 4 is consistent. There's probably more efficient methods, but if this is one you understand and can edit/update yourself it's the easiest IMO.

Re: List of variables based on suffix (not prefix)

ballardw — Thu, 27 Jul 2023 20:50:06 GMT

@_maldini_ wrote:

Apologies. I want to subset an enormous dataset w/ 4962 variables using a KEEP statement. The data are derived from a longitudinal study w/ multiple time points. Variables that begin with "A" represent time point 1. Variables that begin with "B" represent time point 2. Etc.

A good example of a decision made long ago that was wrong. The data for most purposes should have the same variables but one that holds the "time point" information and one observation per timepoint per respondent.

As a changing part based on a source then the "timepoint" should be the suffix, not the actual variable if the main use is going to be looking at the topics and not the timepoints for analysis.

One very common thing with longitudinal data is determining which respondents were available for all times, or when they dropped out (last period of participation) or intermittent responses. This wide format is going to make it very hard to answer those especially if your response values are not able to differentiate between "no value because the participant wasn't reached" and "no value".

Seeing 4000+ variables alone quite often points to a sub-optimal data structure.

Re: List of variables based on suffix (not prefix)

Quentin — Thu, 27 Jul 2023 20:20:13 GMT

This sort of extremely wide dataset becomes problematic to work with very quickly. You've got longitudinal data strung out with one record per patient, and the same question (number of cigarettes smoked today) was asked over nine time-points, so you have nine variable: A09CGTDY to I09CGTDY. (If A-I is the timepoint, what is the meaning of the 09? ). Then presumably you have another question for number of alcoholic drinks, and it would also have 9 variables A09ALTDY to I09ALTDY.

When working with data like this, usually the best first step is to transpose it into a more narrow format. So instead of one patient having 1 row of data with 19 columns (PatientID A09CGTDY-I09CGTDY A09ALTDY-I09ALTDY), one patient has 9 rows of data (one per timepoint) with the columns PatientID Timepoint CGTDY ALTDY.

As you look at your survey data, you may find many other examples of repeating questions which could be further broken out and separated into their own datasets.

We've all been in the position of receiving data in an undesirable format and having to work with it. Life is usually easier if you put in some time to reshape the data before you start analyzing it.

Re: List of variables based on suffix (not prefix)

Quentin — Thu, 27 Jul 2023 20:28:13 GMT

@_maldini_ wrote:

proc sql noprint;
    select name into :colnames separated by ' ' 
    from dictionary.columns
    where substr(name,4) eq 'SMOKE' /* Your suffix goes here */
        and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
        and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
;
quit;


proc sql noprint;
    select name into :colnames2 separated by ' ' 
    from dictionary.columns
    where substr(name,4) eq 'TEST' /* Your suffix goes here */
        and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
        and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
;
quit;

data new_dataset;
    set CARDIA.C1260REQ06_29_2023;
    keep &colnames &colnames2 ID;
run;

If you just want to generate one variable list, you wouldn't need to write multiple PROC SQL steps, you can user an OR operator on the WHERE clause, like:

proc sql noprint;
    select name into :colnames separated by ' ' 
    from dictionary.columns
    where substr(name,4) eq 'SMOKE' or substr(name,4) eq 'ALCOHOL'
        and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
        and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
;
quit;

data new_dataset;
    set CARDIA.C1260REQ06_29_2023;
    keep &colnames ID;
run;

Re: List of variables based on suffix (not prefix)

Quentin — Thu, 27 Jul 2023 20:41:15 GMT

@_maldini_ wrote:

Yikes, you're asking for a lot more questions w/ that idea!

Before I try to adapt that code to play with it, what output will querying the list using PROC SQL produce?

Always a good idea to try it.

The code is very similar to Paige's code. It's using PROC SQL generate a macro variable that holds a list of variable names. Paige's code uses a dictionary table as the source for variable names in a table, and Tom's code uses PROC CONTENTS to output a dataset with the variable names.

Note also Tom's suggestion to use the DATA step to parse the variable names into their logical components. This way you could use PROC SQL to create a list of all variables from timepoint='A', or all variables with variableName='CGTDY'.

A third alternative would be to use a data dictionary / codebook as a datasource. Even when survey developers make poor decisions like creating a data format with thousands of variables, they usually will document the data. So if you have access to a data dictionary that lists all of the variables, with the timepoint, label, etc, often that dictionary can be imported into a dataset and used as a source of helpful metadata.

Re: List of variables based on suffix (not prefix)

Patrick — Fri, 28 Jul 2023 03:06:45 GMT

Often having a narrow and not a wide table makes things easier. But let's assume that the data you've got and need to keep below how you could create your keep list.

1. Create some sample data

data work.have;
  set sashelp.class;
  length A09CGTDY I09CGTDY 8;
run;

2. Select all variables where the name ends with DY (two possible expressions provided).

%let keeplist=;
proc sql noprint;
  select name into :keeplist separated by ' '
  from dictionary.columns
  where 
    libname='WORK' and memname='HAVE' 
    and find(name,'DY','it',length(name)-1)
/*    and prxmatch('/DY$/oi',strip(name))*/
    ;
quit;
%put &=keeplist;

3. Subset the data using &keeplist

data want1;
  set have(keep=&keeplist);
run;

IF the variables you want to keep are in sequence in your table then you can also use double dash notation in your keep option/statement. Proc Contents will show you the order.

proc contents data=work.have order=varnum;
run;quit;

Let's say you want to keep Name and all variables between Height and I09CGTDY then below keep notation would work.

data want2;
  set have(keep=name height--I09CGTDY);
run;

Re: List of variables based on suffix (not prefix)

_maldini_ — Sun, 30 Jul 2023 22:18:09 GMT

I'm doing my best to figure this out. It's confusing for me and I appreciate all your help. I have a partial solution (below) that utilizes the approach provided by @PaigeMiller.

proc sql noprint;
    select name into :colnames separated by ' ' /*  Selects the name variable, which would typically contain column names.;  */
/*  It then places the selected names into a macro variable named colnames, with each name separated by a space.; */
    from dictionary.columns
/*  This line specifies the source of the data, which is the dictionary.columns table in SAS.  */
/*  This is a special table that contains metadata about all available datasets, including column names. */
    where 
    
/* 	Age */
	substr(name,4) eq 'AGE' or
/* 	Education */
	substr(name,4) eq 'ED' or
	
/* 	BMI */
	substr(name,4) eq 'BMI' or
/* 	Note: Neither BMI nor height nor weight are available in the data I have. */

/* 	Exam dates */
/* 	Not including exam date 35 (see below as J02EXDAT) */
	substr(name,2) eq '02DATE' or
	
/*  Smoking status */
    substr(name,2) eq '10SMOKE'
       
        
    and libname='CARDIA' /* Whatever libname is needed goes here, upper case */
    and memname='C1260REQ06_29_2023' /* Your data set name goes here, upper case */
	;
quit;

/***********************************************************************************************************/
/* Creating a subset. */
/* &colnames (from above) is included in the KEEP statement. */
/***********************************************************************************************************/
data cardia.go;
    set CARDIA.C1260REQ06_29_2023;
    keep 
    ID
    SHORT_ID
	SEX
	race

/*  Suffix Variables  */
    &colnames

/*  Preffix Variables  */    
	CENTER:    
	
/* 	Per Lucia: The exam at Y35 was carried out in two phases. We are still discussing the best way to assemble a Reference File for this exam.  */
/* 	We have variable J02EXDAT. */
	J02EXDAT
	
/* 	TAKING DIABETES MEDS (1=NO,2=YES) at baseline */
	FDIABMED
    ;

    label J02EXDAT="DATE OF EXAM 10";
run;

The above syntax allows me to use a list based on a suffix in a KEEP statement to create a subset (per my original post).

I would also like to be able to subset by prefix (e.g., subset all the variables from a particular timepoint; "A" = baseline). @Tom offered an approach that looks like it might offer that, but I can't figure out how to go from the PROC SQL to a dataset. When I run a PROC PRINT on the "groups" dataset the variables are all under the column "names" and the other columns are all the metadata categories:

Using what I've adapted from @PaigeMiller above (because at least I understand that part), Is there a way to create a variable that would allow me to subset by prefix?

data cardia.go_a;
  set cardia.go;
  where prefix = "A";
run;

Thanks for your help.