BookmarkSubscribeRSS Feed
Itharaju
Fluorite | Level 6

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.

 

4 REPLIES 4
jimbarbour
Meteorite | Level 14

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:

jimbarbour_0-1600747524509.png

 

How does that look?

 

Jim

Shmuel
Garnet | Level 18

data want;

 set have;

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

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

run;

andreas_lds
Jade | Level 19

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;
hhinohar
Quartz | Level 8

 

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 646 views
  • 0 likes
  • 5 in conversation