missing column

[HeatherNewton]

data &Portoffo._VAL(keep=MOB DQ_6);

set monthly.score_&Portofo._VAL(keep=account_no CURR_DELQ MONTH_ON_BOOK bad);

where bad in ('1' '0');

run;

I cannot find 'bad' column in my dataset month.score_&Portofo., could it be created by the clause 'where bad in ('1' '0');

or it has to be from dataset month.score_&Portofo.??

[SASKiwi]

WHERE statements don't create variables, they just filter your input dataset. What does your SAS log report? I suspect it contains an error about BAD not existing in your input dataset  - monthly.score_&Portofo._VAL.

[Tom]

---

@HeatherNewton wrote:

data &Portoffo._VAL(keep=MOB DQ_6);

set monthly.score_&Portofo._VAL(keep=account_no CURR_DELQ MONTH_ON_BOOK bad);

where bad in ('1' '0');

run;

I cannot find 'bad' column in my dataset month.score_&Portofo., could it be created by the clause 'where bad in ('1' '0');

or it has to be from dataset month.score_&Portofo.??

---

The program is not reading from  month.score_&Portofo.  it is reading from monthly.score_&Portofo._VAL.  Notice that in addition to using a different dataset name you are using a different libref also.

Do you have the dataset that the program is reading?  Does it have the BAD variable?  

If you don't have that dataset then there is probably some other step that is supposed to create the dataset that does have the BAD variable.  Sounds like some type of validation process from the variable and dataset names.

[HeatherNewton]

sorry I meant the data set 

&Portoffo._VAL

it was created in another sas program but without 'bad' 

[Tom]

---

@HeatherNewton wrote:

sorry I meant the data set 

&Portoffo._VAL

it was created in another sas program but without 'bad' 

---

Then you seem to be missing part of your program.

[Kurt_Bremser]

Then the code will fail and it means the whole process is probably crap.

As I have told you repeatedly, with crap code like you get, it's best to go back to the people which use the results and have them describe the whole process (from initial data to expected information retrieved from it) in non-SAS terms. If they can't, discard the process. A process where nobody really knows what it does (or should do) is useless at best, but probably dangerous and toxic.

And let this be an object lesson to your future self: document, document, document. Have clear descriptions of input and output data. Maybe your "bad" column was in an Excel sheet delivered by someone from outside once and is no longer there. Since Excel sheets require the unreliable IMPORT, missing columns go undetected, scrambling the further process.

Or you simply read from a database, and someone removed the column there without informing the people "downstream". Been there, done that (fixing the SAS process afterward), have a closet of T-shirts.

[HeatherNewton]

thank you guys, I found the root cause

some other team mates miss them in their mapping a few sas programs before my one.. 

I learnt the lesson I am reviewing the series of programs and any remaining documentations...to get the grip of what they are doing