topic Need to derive the new columns from existing column and subract with previous values on month basis in SAS Programming

Need to derive the new columns from existing column and subract with previous values on month basis

Itharaju — Tue, 22 Sep 2020 03:29:31 GMT

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.

Re: Need to derive the new columns from existing column and subract with previous values on month ba

jimbarbour — Tue, 22 Sep 2020 04:05:41 GMT

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

Re: Need to derive the new columns from existing column and subract with previous values on month ba

Shmuel — Tue, 22 Sep 2020 04:17:21 GMT

data want;

set have;

ws_out = catx('-',wholeale,lag(wholeale));

retail_out = catx('-',retail,lag(retail));

run;

Re: Need to derive the new columns from existing column and subract with previous values on month ba

andreas_lds — Tue, 22 Sep 2020 05:13:03 GMT

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;

Re: Need to derive the new columns from existing column and subract with previous values on month ba

hhinohar — Wed, 23 Sep 2020 13:26:03 GMT

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;