Transposed Data with unexpected result

WorkingMan — Thu, 18 Oct 2018 08:25:02 GMT

Hi, I have a table and i transpose it from long to wide. Since I am rushing for a flight, I havent got time to post any sample dataline data here.

To cut short, let me go straight to the topic with screenshots.

My table that has "long" data. Notice that 1 ID = 1 set.

And my transposed data is as below:

Notice that for newID = 25, the transposed table has 2 separate rows. However, this does not happen to newID 23 and 24. I would expect the record of newID 25 to only have 1 row, which have st_datetime and sp_datetime accordingly, not splitting into 2 different rows.

Here are the command:

Final command:
proc transpose data=countstsp out=finalscadajoinedtranstable;
	by newID mnemonic_psi substationname voltagetype st_power sp_power;
	id _NAME_ ;
	var COL1 ;
	
run;

Also, before the command above, I have another command that produces similar result set(which newID 25 has 2 rows in transposed set)

proc transpose data=countstsp
out=finalscadajoinedtranstable;
	by newID mnemonic_psi substationname voltagetype ;
	id _NAME_ ;
	var COL1 ;
	copy  st_power sp_power;
run;

Which part did i do wrong? I couldn't get the logic right here.

Re: Transposed Data with unexpected result

ballardw — Thu, 18 Oct 2018 14:14:59 GMT

Every unique combination of your BY variables generates an output line.

In the shown values for Newid=23 and 24 you have no records showing values for substationname (and the other by variables) but Newid=25 has records with both missing values and at least one value. So missing plus one substation value = 2 output records.

topic Transposed Data with unexpected result in SAS Programming

Transposed Data with unexpected result

Re: Transposed Data with unexpected result