Data pull based on flag value

[AJ_Brien]

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;

[novinosrin · Accepted solution]

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;

[AJ_Brien]

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.

[novinosrin]

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

[AJ_Brien]

that makes sense. Thank you!

[novinosrin]

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;