topic Re: SAS Program in SAS Programming

How to create a new variable based on value in one column across the group

jaiganesh — Mon, 18 Nov 2019 17:01:22 GMT

Hello ,

I've a data like below

Part_No Remark

kk12 READY TO SELL

kk12 Hold in STOCK

kk22 READY TO SELL

kk22 Hold in STOCK

kk33 Hold in STOCK

I need a output like

Part_No Remark conclusion

kk12 READY TO SELL Hold in STOCK

kk12 Hold in STOCK Hold in STOCK

kk22 READY TO SELL Hold in STOCK

kk22 Hold in STOCK Hold in STOCK

kk33 READY TO SELL READY TO SELL

Need a column conclusion , Where the any Part_No has any Remark "Hold in STOCK " , Then conclusion would be all "Hold in STOCK" else conclusion would be "READY TO SELL".

Any Remark for the Part_No has "Hold in STOCK" should be given the conclusion as "Hold in STOCK"

Kindly respond with SAS Code

Re: SAS Program

Kurt_Bremser — Mon, 18 Nov 2019 11:10:13 GMT

@jaiganesh wrote:

Hello ,

I've a data like below

Part_No Remark

kk12 READY TO SELL

kk12 Hold in STOCK

kk22   READY TO SELL

kk22   READY TO SELL

kk22   Hold in STOCK

kk33   Hold in STOCK

kk33   Hold in STOCK

I need a output like

Part_No Remark conclusion

kk12 READY TO SELL   Hold in STOCK

kk12 Hold in STOCK Hold in STOCK

kk22   READY TO SELL   Hold in STOCK

kk22   READY TO SELL   Hold in STOCK

kk22   Hold in STOCK Hold in STOCK

kk33 READY TO SELL   READY TO SELL

kk33 READY TO SELL   READY TO SELL

Need a column conclusion , Where the any Part_No has any Remark "Hold in STOCK " , Then conclusion would be all "Hold in STOCK" else conclusion would be "READY TO SELL".

Any Remark for the Part_No has "Hold in STOCK" should be given the conclusion as "Hold in STOCK"

Kindly respond with SAS Code

Your rules do not explain your "want" values for kk33. There's not a single "READY TO SELL" in the "have" data.

Re: SAS Program

Jagadishkatam — Mon, 18 Nov 2019 11:11:00 GMT

data have;
input Part_No$ Remark&$100.;
cards;
kk12 READY TO SELL
kk12 Hold in STOCK
kk22 READY TO SELL
kk22 READY TO SELL
kk22 Hold in STOCK
kk33 Hold in STOCK
kk33 Hold in STOCK
;

data want;
do until(last.Part_No);
set have;
by Part_No;
if last.Part_No then conclusion=Remark;
end;
do until(last.Part_No);
set have;
by Part_No;
output;
end;
run;

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:15:11 GMT

I think what i ask was, I have the input data set (first ) and need a output like 2nd data set.

Where has conclusion column , Which is writing the conclusion on the basis of remark, If any of the remark say "Hold in STOCK " for individual part then , Entire Conclusion should say "Hold in STOCK " (for all individual lines in conclusion) else it should say " READY TO SELL"

Let me know if you need any other clarification.

Re: SAS Program

PeterClemmensen — Mon, 18 Nov 2019 11:20:43 GMT

What is the logic behind that conclusion='READY TO SELL' for Part_No =kk33 ?

Re: SAS Program

Kurt_Bremser — Mon, 18 Nov 2019 11:27:51 GMT

@jaiganesh wrote:

I think what i ask was, I have the input data set (first ) and need a output like 2nd data set.

Where has conclusion column , Which is writing the conclusion on the basis of remark, If any of the remark say "Hold in STOCK " for individual part then , Entire Conclusion should say "Hold in STOCK " (for all individual lines in conclusion) else it should say " READY TO SELL"

Let me know if you need any other clarification.

Still does not make sense at all. What should be the result for kk33, and WHY?

And please show the code you tried on your own, I'm into helping, not into doing someone else's work for free.

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:33:27 GMT

What i mean is , If any remark for any part says "Hold in STOCK" then entire conclusion should be shown as "Hold in STOCK" else "Ready to Sell", Actual requirement is different, However this logic could be significant part of it.

I would appreciated if you can help to guide in any ways.

Re: SAS Program

PeterClemmensen — Mon, 18 Nov 2019 11:36:07 GMT

If that is the logic, then would you agree that your posted result is incorrect?

Re: SAS Program

Kurt_Bremser — Mon, 18 Nov 2019 11:37:57 GMT

@jaiganesh wrote:

What i mean is , If any remark for any part says "Hold in STOCK" then entire conclusion should be shown as "Hold in STOCK" else "Ready to Sell", Actual requirement is different, However this logic could be significant part of it.

I would appreciated if you can help to guide in any ways.

Then your posted result for kk33 is wrong. Please correct it, or change the rules so that they explain the expected result. And show the code you tried.

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:46:59 GMT

This would not help , As we are taking only last Remark for all parts, However my concern is , Which ever part has ANY Remark "HOLD ON STOCK " should come Conclusion as "HOLD ON STOCK " for all line for that parts, Else "Ready to sell" for all Lines.

Thanks in Advence

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:50:27 GMT

Ohhh, I'm sorry , That was my mistake , Please consider below onc.

I've a data like below

Part_No Remark

kk12 READY TO SELL

kk12 Hold in STOCK

kk22 READY TO SELL

kk22 Hold in STOCK

kk33 Hold in STOCK

kk33 READY TO SELL

I need a output like

Part_No Remark conclusion

kk12 READY TO SELL Hold in STOCK

kk12 Hold in STOCK Hold in STOCK

kk22 READY TO SELL Hold in STOCK

kk22 Hold in STOCK Hold in STOCK

kk33 Hold in STOCK Hold in STOCK

kk33 READY TO SELL Hold in STOCK

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:50:57 GMT

Ohhh, I'm sorry , That was my mistake , Please consider below onc.

I've a data like below

Part_No Remark

kk12 READY TO SELL

kk12 Hold in STOCK

kk22 READY TO SELL

kk22 Hold in STOCK

kk33 Hold in STOCK

kk33 READY TO SELL

I need a output like

Part_No Remark conclusion

kk12 READY TO SELL Hold in STOCK

kk12 Hold in STOCK Hold in STOCK

kk22 READY TO SELL Hold in STOCK

kk22 Hold in STOCK Hold in STOCK

kk33 Hold in STOCK Hold in STOCK

kk33 READY TO SELL Hold in STOCK

Please suggest on this

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 11:51:43 GMT

Ohhh, I'm sorry , That was my mistake , Please consider below one.

I've a data like below

Part_No Remark

kk12 READY TO SELL

kk12 Hold in STOCK

kk22 READY TO SELL

kk22 Hold in STOCK

kk33 Hold in STOCK

kk33 READY TO SELL

I need a output like

Part_No Remark conclusion

kk12 READY TO SELL Hold in STOCK

kk12 Hold in STOCK Hold in STOCK

kk22 READY TO SELL Hold in STOCK

kk22 Hold in STOCK Hold in STOCK

kk33 Hold in STOCK Hold in STOCK

kk33 READY TO SELL Hold in STOCK

Please suggest on this

Re: SAS Program

PeterClemmensen — Mon, 18 Nov 2019 11:54:24 GMT

@jaiganesh good. @Jagadishkatam gave you a working solution.

Re: SAS Program

Jagadishkatam — Mon, 18 Nov 2019 11:56:09 GMT

Please try the below code, only change to my previous code is i included an additional proc sort step

data have;
input Part_No$ Remark&$100.;
cards;
kk12 READY TO SELL
kk12 Hold in STOCK
kk22 READY TO SELL
kk22 READY TO SELL
kk22 Hold in STOCK
kk33 Hold in STOCK
kk33 READY TO SELL
;

PROC SORT DATA=have;
BY Part_No DESCENDING Remark;
RUN;

data want;
do until(last.Part_No);
set have;
by Part_No;
if last.Part_No then conclusion=Remark;
end;
do until(last.Part_No);
set have;
by Part_No;
output;
end;
run;

Re: SAS Program

Kurt_Bremser — Mon, 18 Nov 2019 11:56:29 GMT

And what have you tried already?

Re: SAS Program

jaiganesh — Mon, 18 Nov 2019 12:35:44 GMT

I've tried doing that with Some proc SQL Statement, However couldn't able to get the Robust solutions on this.

Re: SAS Program

Kurt_Bremser — Mon, 18 Nov 2019 12:39:55 GMT

@jaiganesh wrote:

I've tried doing that with Some proc SQL Statement, However couldn't able to get the Robust solutions on this.

Show it, so we can tell you where your thinking went wrong. You learn most from your mistakes.

Re: SAS Program

Astounding — Mon, 18 Nov 2019 16:11:13 GMT

Good observation. Some of the suggestions so far may work. However ...

If it is important to maintain the current order of the observations, and

If all values of your REMARK variable are non-blank,

you could try it this way:

data want;
conclusion = 'READY TO SELL';
do until(last.Part_No);
   set have;
   by Part_No;
   if remark = 'Hold in STOCK' then conclusion = remark;
end;
do until(last.Part_No);
  set have;
  by Part_No;
  output;
end;
run;