topic Re: Proc transpose not showing all date instances in SAS Programming

Proc transpose not showing all date instances

Q1983 — Thu, 03 Oct 2019 20:20:17 GMT

data have;

infile datalines ;

input ln $ Date1 Date9. code $ ;

format date1 date9.;

datalines ;

0013 26Jul2019 K11

0013 29Jul2019 K11

0013 29Jul2019 K12

0013 30Jul2019 K12

0033 30Jul2019 A13

0033 31Aug2019 A13

0034 1Aug2019 B9

0034 2Aug2019 B10

;run;

proc sort data=have nodupkey;by ln date1;run;

proc transpose data=have out=have2 (drop= _NAME_ _LABEL_) prefix=_ let ;

id code;

by ln ;

var date1;

run;

Notice that 0013 29Jul2019 K11 shows up but not 0013 26Jul2019 K11. I know we are transposing based on code, ln and date1 however is there a way to show all instances side by side?

Re: Proc transpose not showing all date instances

Kurt_Bremser — Thu, 03 Oct 2019 20:27:56 GMT

You should get a message that K11 appears twice in group 0013. Please show us an example of the expected output.

Re: Proc transpose not showing all date instances

data_null__ — Thu, 03 Oct 2019 20:30:59 GMT

To save time show example of your desired output.

Re: Proc transpose not showing all date instances

Q1983 — Thu, 03 Oct 2019 20:36:48 GMT

WARNING: The ID value "_K11" occurs twice in the same BY group.
Yes I do get that message.
So is there a way to show the 7/26/19 K11 entry also
ln _K11 _K11 _K12 _A13 _B9 _B10
0013 26Jul2019 29Jul2019 30Jul2019
0033 31Aug2019
0034 01Aug2019 02Aug2019

Re: Proc transpose not showing all date instances

Q1983 — Thu, 03 Oct 2019 20:38:04 GMT

Desired output

ln	_K11	_K11	_K12	_A13	_B9	_B10
0013	26Jul2019	29Jul2019	30Jul2019
0033				31Aug2019
0034					01Aug2019	02Aug2019

So I also want to show the first instance of _K11 that occurred on 7/26/19

Re: Proc transpose not showing all date instances

Tom — Thu, 03 Oct 2019 20:44:28 GMT

@Q1983 wrote:

Desired output

ln _K11 _K11 _K12 _A13 _B9 _B10

0013 26Jul2019 29Jul2019 30Jul2019

0033 31Aug2019

0034 01Aug2019 02Aug2019

So I also want to show the first instance of _K11 that occurred on 7/26/19

And what variable name do you propose for this second copy of _K11 ?

Are you sure you don't want a report instead of a dataset?