topic Re: Data pull based on flag value in SAS Programming

Data pull based on flag value

AJ_Brien — Thu, 08 Aug 2019 16:26:11 GMT

Hello,

I'm trying to select all observations where flag is not equal to 'Y' in the previously and set those to Y in the current data pull. It felt like a simple logic, but I don't seem to be getting the right answer. Appreciate the help in helping me understand what am I doing wrong here.

Input data provided in code.

Expected output:

Output received:

my code

data dat1;
 input id $ flag $ new zz;
 cards;
a Y 12 111
b   22 222
c   33 333
 ;
 run;


 proc sql; 
        create table pilot as 
select *, 'Y' as flag
     from dat1 
      	  where flag neq 'Y'; 
quit;

Re: Data pull based on flag value

novinosrin — Thu, 08 Aug 2019 16:32:55 GMT


data dat1;
 input id $ flag $ new zz;
 cards;
a Y 12 111
b  . 22 222/*see the correction for period in place of missing values*/
c  . 33 333/*see the correction for period in place of missing values*/
 ;
 run;


 proc sql; 
        create table pilot as 
select id, 'Y' as flag,new ,zz
     from dat1 
      	  where flag ne 'Y'; 
quit;

Re: Data pull based on flag value

AJ_Brien — Thu, 08 Aug 2019 16:37:36 GMT

That made it work but isn't char missing value represented by blank and not a '.'? flag is defined as a char here so why are we representing that as a period.

Re: Data pull based on flag value

novinosrin — Thu, 08 Aug 2019 16:42:22 GMT

Those values before being read are "RAW" data and not a SAS data set value. basically to read raw data instream with a list input you would need to have periods in punch raw cards data.

PS Your definition holds true for default missing values in a dataset

Re: Data pull based on flag value

novinosrin — Thu, 08 Aug 2019 16:45:54 GMT

The thread could lead to discussion about "how to read raw data" which is out of scope for the stated objective, but I recommend reading those concepts.

You could read the same data as- is with a formatted input to see the difference

data dat1;
 input id $ @3 flag $1. new zz;
 cards;
a Y 12 111
b   22 222
c   33 333
 ;
 run;

Re: Data pull based on flag value

AJ_Brien — Thu, 08 Aug 2019 16:59:22 GMT

that makes sense. Thank you!