CODEWITHSUNDEEP
sas
Plug4
Plug4

Reputation: 3948

SAS: Drop column in a if statement

I have a dataset called have with one entry with multiple variables that look like this:

message reference time  qty  price 
x       101       35000 100  .

the above dataset changes every time in a loop where message can be ="A". If the message="X" then this means to remove 100 qty from the MASTER set where the reference number equals the reference number in the MASTER database. The price=. is because it is already in the MASTER database under reference=101. The MASTER database aggregates all the available orders at some price with quantity available. If in the next loop message="A" then the have dataset would look like this:

message reference time qty price A 102 35010 150 500

then this mean to add a new reference number to the MASTER database. In other words, to append the line to the MASTER.

I have the following code in my loop to update the quantity in my MASTER database when there is a message X:

data b.master;
modify b.master have(where=(message="X")) updatemode=nomissingcheck;
by order_reference_number;
if _iorc_ = %sysrc(_SOK) then do;
replace;
end;
else if _iorc_ = %sysrc(_DSENMR) then do;
      output;
      _error_ = 0;
    end;
else if _iorc_ = %sysrc(_DSEMTR) then do;
      _error_ = 0;
end;
else if _iorc_ = %sysrc(_DSENOM) then do;
_error_ = 0;
end;
run;

I use the replace to update the quantity. But since my entry for price=. when message is X, the above code sets the price='.' where reference=101 in the MASTER via the replace statement...which I don't want. Hence, I prefer to delete the price column is message=X in the have dataset. But I don't want to delete column price when message=A since I use this code 

proc append base=MASTER data=have(where=(msg_type="A")) force;
run;

Hence, I have this code price to my Modify statement:

data have(drop=price_alt); 
set have; if message="X" then do; 
output;end; 
else do; /*I WANT TO MAKE NO CHANGE*/ 
end;run;

but it doesn't do what I want. If the message is not equal X then I don't want to drop the column. If it is equal X, I want to drop the column. How can I adapt the code above to make it work?

Upvotes: 0

Views: 5933

Answers (2)

DJM
DJM

Reputation: 169

Its a bit of a strange request to be honest, such that it raises questions about whether what you're doing is the best way of doing it. However, in the spirit of answering the question...

The answer by DomPazz gives the option of splitting the data into two possible sets, but if you want code down the line to always refer to a specific data set, this creates its own complications.

You also can't, in the one data step, tell SAS to output to the "same" data set where one instance has a column and one instance doesn't. So what you'd like, therefor, is for the code itself to be dynamic, so that the data step that exists is either one that does drop the column, or one that does not drop the column, depending on whether message=x. The answer to this, dynamic code, like many things in SAS, resolves to the creative use of macros. And it looks something like this:

/* Just making your input data set */

data have;
message='x';
time=35000;
qty=1000;
price=10.05;
price_alt=10.6;
run;

/* Writing the macro */

%macro solution;
    %local id rc1 rc2;

    %let id=%sysfunc(open(work.have));
    %syscall set(id);
    %let rc1=%sysfunc(fetchobs(&id, 1));
    %let rc2=%sysfunc(close(&id));

%IF &message=x %THEN %DO;
    data have(drop=price_alt);
    set have;
    run;
%END;

%ELSE %DO;
    data have;
    set have;
    run;
%END;
%mend solution;

/* Running the macro */

%solution;

Upvotes: 3

DomPazz
DomPazz

Reputation: 12465

Try this:

data outX(drop=price_alt) outNoX;
set have;
if message = "X" then
   output outX;
else
   output outNoX;
run;

As @sasfrog says in the comments, a table either has a column or it does not. If you want to subset things where MESSAGE="X" then you can use something like this to create 2 data sets.

Upvotes: 3

Related Questions