Solved: Re: SAS Program

jaiganesh · Posted 11-18-2019 05:56 AM

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

Jagadishkatam · Posted 11-18-2019 06:56 AM

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;

Thanks,
Jag

View solution in original post

Kurt_Bremser · Posted 11-18-2019 06:10 AM

@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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

jaiganesh · Posted 11-18-2019 06:15 AM

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.

Kurt_Bremser · Posted 11-18-2019 06:27 AM

@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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

jaiganesh · Posted 11-18-2019 06:33 AM

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.

PeterClemmensen · Posted 11-18-2019 06:36 AM

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

The DATA to DATA Step Macro
Blog: SASnrd

jaiganesh · Posted 11-18-2019 06:50 AM

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

Kurt_Bremser · Posted 11-18-2019 06:56 AM

And what have you tried already?

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

jaiganesh · Posted 11-18-2019 07:35 AM

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

Kurt_Bremser · Posted 11-18-2019 07:39 AM

@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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Kurt_Bremser · Posted 11-18-2019 06:37 AM

@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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

jaiganesh · Posted 11-18-2019 06:50 AM

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

PeterClemmensen · Posted 11-18-2019 06:54 AM

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

The DATA to DATA Step Macro
Blog: SASnrd

Jagadishkatam · Posted 11-18-2019 06:11 AM

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;

Thanks,
Jag

jaiganesh · Posted 11-18-2019 06:46 AM

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

Classroom Training Available!