Hello All ,
PRODUCT MONTH WHOLEALE RETIAL WS_OUT RETIAL_OUT
CREDIT 01MAR2020 12 20 12-0 20-0
CREDIT 01APR2020 4 35 4-12 35-20
CREDIT 01MAY2020 2 54 2-4 54-35
CREDIT 01JUN2020 3 29 3-2 29-54
CREDIT 01JUL2020 11 13 11-3 13-29
CREDIT 01AUG2020 5 98 5-11 98-13
AVIA
MARG
etc.
from above table and i need to derived the new columns called WS_OUT and RETAIL_OUT which i shown above.
And same I have multiple product's like AVIA,MARG,HELTH etc. for every months.
Thanks in advance for your help on this.
How about something like this?
DATA Product_Data;
INFILE DATALINES4
FIRSTOBS = 2;
FORMAT Month YYMMDDD10.;
LENGTH Product $25
Month 8
Wholesale $4
Retail $4
;
INPUT Product $
Month : ANYDTDTE7.
Wholesale $
Retail $
;
DATALINES4;
PRODUCT MONTH WHOLEALE RETIAL
CREDIT 01MAR2020 12 20
CREDIT 01APR2020 4 35
CREDIT 01MAY2020 2 54
CREDIT 01JUN2020 3 29
CREDIT 01JUL2020 11 13
CREDIT 01AUG2020 5 98
;;;;
RUN;
**------------------------------------------------------------------------------**;
DATA Product_Data_And_Out;
SET Product_Data;
DROP _:;
LENGTH _Prior_Wholesale $4;
LENGTH _Prior_Retail $4;
LENGTH WS_Out $10;
LENGTH Retail_Out $10;
RETAIN _Prior_Wholesale;
RETAIN _Prior_Retail;
IF _N_ = 1 THEN
DO;
WS_OUT = CATS(Wholesale, '-', '0');
Retail_OUT = CATS(Retail, '-', '0');
END;
ELSE
DO;
WS_OUT = CATS(Wholesale, '-', _Prior_Wholesale);
Retail_OUT = CATS(Retail, '-', _Prior_Retail);
END;
OUTPUT;
_Prior_Wholesale = Wholesale;
_Prior_Retail = Retail;
RUN;
Results:
How does that look?
Jim
data want;
set have;
ws_out = catx('-',wholeale,lag(wholeale));
retail_out = catx('-',retail,lag(retail));
run;
The following data-step is just an extend version of the answer posted by @Shmuel taking into account that you " have multiple product's like AVIA,MARG,HELTH" and assuming that the data is at least grouped by product. The code is, of course, untested, because you have not provided data in usable form.
data want;
set have;
by product notsorted;
_wholesale = lag(wholesale);
_retail = lag(retail);
if first.product then do;
_wholesale = 0;
_retail = 0;
end;
ws_out = catx('-', wholesale, _wholesale);
retail_out = catx('-', retail, _retail);
drop _wholesale _retail;
run;
data have;
infile datalines dlm="09"x;
format month yymmdds10.;
input PRODUCT $ MONTH:date9. WHOLESALE RETAIL;
datalines;
CREDIT 01MAR2020 12 20
CREDIT 01APR2020 4 35
CREDIT 01MAY2020 2 54
CREDIT 01JUN2020 3 29
CREDIT 01JUL2020 11 13
CREDIT 01AUG2020 5 98
;
run;
data want;
set have;
ws_out=cats(wholesale,"-",ifn(lag(wholesale)=.,0,lag(wholesale)));
retail_out=cats(retail,"-",ifn(lag(retail)=.,0,lag(retail)));
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.