BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SB123
Calcite | Level 5

Hi SAS Experts,

i have a SAS dataset with 250 fields which mainly indicates flags (either has 1 or 0 or null). i have to pull data from that dataset
where field name starts with a specific letter and ends with word "RESULT" (Ex: starts with letter "P" and ends with
word "RESULT" - Process1_RESULT, Process2_RESULT ... and so on) and where none of the field has "1" in it.
I was able to do this when i had less fields and i can manually type the field names and say where process1_result ne '1' ,
 however sometimes i need to check this in 200 fields and you don't want to type all those 200 fields. Is there a better way to do this without having to type all the field names. All the fields are in character format.

 

Dataset structure:

System_IDDateFinal_StatusProcess1_ResultProcess2_ResultProcess3_ResultProcess4_Result
1234531-Jul-17Y0010
6789125-Jul-17N 0 0
5566929-Jul-17N101 

 

From the above table, i would only want to get row 2 where System_ID is 67891 since none of the flag fields has 1 in it. In the above example i have only 4 flag fields however, in my actual datasets there is around 250 flag fields where i have to check whether it has "1" in it or not.

 

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@SB123 wrote:

Is there a better way to do this without having to type all the field names..


The best thing would be to create the variable names with the number at the END of the variable name instead of in the middle. Then you could just variable lists. Either a range of such named values. Or perhaps all variables that start with a certain prefix.

Process_Result1 - Process_Result25
Process_Result:

With your current table you can query the metadata about the variables.  You could use DICTIONARY tables in PROC SQL. Or just use PROC CONTENTS to generate the list of variables into a dataset and work from that.

proc contents data=have noprint out=contents;
run;
proc sql noprint ;
select varnum,name
  into :dummy,:namelist separated by ' '
  from contents
  where upcase(name) like 'PROCESS%_RESULT'
  order by varnum
;
quit;

So then you could use this new NAMELIST macro variable in your code in the place where you might use a list of variable names. So perhaps in an ARRAY defintion. Or perhaps in some other places where variable lists are accepted.

 

* Get subset of data with NONE of flags set to '1' ;
data want ;
  set have ;
  if whichc('1',of &namelist) then delete ;
run;

View solution in original post

5 REPLIES 5
Reeza
Super User

Use an array with a variable short cut list.

 

Are the variables side by side in the data set?

If so you can reference them as:

 

array check_vars(*) process1_result -- process200_result;

If you moved the index to the end of the word you can just use:

 

array check_vars(*) process_result1-process_Result200;

Then I would suggest basic math, add up all the values and if it's greater than one, then do whatever logic you need.

 

if sum(of check_vars(*)) > 1 then ...;
SB123
Calcite | Level 5

Hi Reeza,

The variable are side by side, however, it will not be in numeric order as you mentioned. All the field names are not like process1_result, process2_result ,, there are field which are named as like processduplicate_result, processfinal_result. That is why i mentioned starts with a certain letter "P" and ends with word "Result".

Reeza
Super User

The double dash (--) tells SAS this is the first variable in my list and use all variables to the end of the my variable list. So if they're side by side, this works fine. 

Tom
Super User Tom
Super User

@SB123 wrote:

Is there a better way to do this without having to type all the field names..


The best thing would be to create the variable names with the number at the END of the variable name instead of in the middle. Then you could just variable lists. Either a range of such named values. Or perhaps all variables that start with a certain prefix.

Process_Result1 - Process_Result25
Process_Result:

With your current table you can query the metadata about the variables.  You could use DICTIONARY tables in PROC SQL. Or just use PROC CONTENTS to generate the list of variables into a dataset and work from that.

proc contents data=have noprint out=contents;
run;
proc sql noprint ;
select varnum,name
  into :dummy,:namelist separated by ' '
  from contents
  where upcase(name) like 'PROCESS%_RESULT'
  order by varnum
;
quit;

So then you could use this new NAMELIST macro variable in your code in the place where you might use a list of variable names. So perhaps in an ARRAY defintion. Or perhaps in some other places where variable lists are accepted.

 

* Get subset of data with NONE of flags set to '1' ;
data want ;
  set have ;
  if whichc('1',of &namelist) then delete ;
run;
SB123
Calcite | Level 5

Thanks Tom and Reeza. Both of your solutions work.

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
  • 5 replies
  • 972 views
  • 0 likes
  • 3 in conversation