DATA Step, Macro, Functions and more

Pull data from a dataset where none of the fields has 1

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Pull data from a dataset where none of the fields has 1

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.


Accepted Solutions
Solution
‎07-31-2017 02:04 PM
Super User
Super User
Posts: 7,055

Re: Pull data from a dataset where none of the fields has 1

[ Edited ]

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


All Replies
Super User
Posts: 19,817

Re: Pull data from a dataset where none of the fields has 1

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 ...;
New Contributor
Posts: 3

Re: Pull data from a dataset where none of the fields has 1

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".

Super User
Posts: 19,817

Re: Pull data from a dataset where none of the fields has 1

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. 

Solution
‎07-31-2017 02:04 PM
Super User
Super User
Posts: 7,055

Re: Pull data from a dataset where none of the fields has 1

[ Edited ]

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

Re: Pull data from a dataset where none of the fields has 1

Thanks Tom and Reeza. Both of your solutions work.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 204 views
  • 0 likes
  • 3 in conversation