topic Re: Sorting data by text and date in SAS Programming

Sorting data by text and date

Pradeepbanu — Sun, 21 Jun 2020 11:43:43 GMT

Hello all,

My data looks like

Source	Date	Targets	count
Email	7-Feb-20	TGT	100
Email	7-Feb-20	NON-TGT	10
Web	8-Feb-20	TGT	50
Web	8-Feb-20	NON-TGT	20
Live	9-Feb-20	TGT	40
Live	9-Feb-20	NON-TGT	5
None	10-Feb-20	TGT	20

This what I am doing

PROC SORT DATA =DATA_1 OUT = DATA_1_SORT;
BY SOURCE Date Targets;
RUN;

DATA DATA_1_SORT;
SET DATA_1_SORT;
BY SOURCE Date Targets;
IF FIRST.Date;

run;

Then Transposing

options validvarname=any;
PROC TRANSPOSE DATA =DATA_1_SORT PREFIX = _ OUT = TRN_DATA_1_SORT let;
WHERE Targets='TGT';
BY Source;
ID WEEK_END;
VAR COUNT;
RUN;

While sorting I am getting the error BY variable are not sorted properly. My output would look like below

Source	Targets	7-Feb-20	8-Feb-20	9-Feb-20	10-Feb-20
Email	TGT	100
Web	TGT		50
Live	TGT			40
None	TGT				20

In this case how will I sort to achieve this transpose data? Error I am getting here is BY variable not sorted properly!!

Thanks

Re: Sorting data by text and date

novinosrin — Sun, 21 Jun 2020 12:05:53 GMT

Your sample suggests dates are already sorted in source data_1.

So-

data DATA_1;
input Source $	Date :date9.	Targets	$ count;
format date date9.;
cards;
Email	7-Feb-20	TGT	100
Email	7-Feb-20	NON-TGT	10
Web	8-Feb-20	TGT	50
Web	8-Feb-20	NON-TGT	20
Live	9-Feb-20	TGT	40
Live	9-Feb-20	NON-TGT	5
None	10-Feb-20	TGT	20
;

PROC TRANSPOSE DATA =DATA_1 PREFIX = _ OUT = TRN_DATA_1_SORT(drop=_name_) let;
WHERE Targets='TGT';
BY Source notsorted;
VAR COUNT;
id date;
RUN;

Re: Sorting data by text and date

ballardw — Sun, 21 Jun 2020 17:49:48 GMT

Please show the log for everything from your Proc Sort through the Proc transpose code. Copy all of the code, notes, messages and errors from the Log, paste into a code box opened with the </> to preserve any formatting of error messages.

Since you have 3 places using the same BY variables it is not clear which one might actually be causing the error.