topic Transpose transformation : SAS DI in SAS Data Management

Transpose transformation : SAS DI

JJP1 — Mon, 12 Aug 2019 07:17:41 GMT

Hi ,

Please help on SAS DI transpose transformation option to get required output please.

Iam not getting any columns created like Valu6,Value 7...i need it till Value12 please.just to ensure that there is no value for this in final output please.

Input Data to be transposed

Value

NAME

FILE1

Status

SSSSS

567Q44104

DADA

Not Applicable

SSSSS

20726910

Accepted

Banking

567Q44104

DADA

Not Applicable

AAG Portf

567Q44104

DADA

Not Applicable

AAG Portf

S68855kl

Not Applicable

Failed

S68855kl

Not Applicable

Cache

S68855kl

Not Applicable

AAG Txn

567Q44104

DADA

Not Applicable

AAG Txn

S68855kl

Not Applicable

AAG Txn

20726910

Accepted

ADF

20718435

Accepted

ADG

20726910

Accepted

CXE

S68855kl

Not Applicable

Currently Getting Output

NAME

Value1

Value2

Value3

Value4

Value5

FILE1

Status

567Q44104

SSSSS

Banking

AAG Txn

DADA

Not Applicable

20726910

SSSSS

AAG Txn

ADG

Accepted

Required Output (SAS DI option to be mentioned please)

NAME

Value1

Value2

Value3

Value4

Value5

Value6

Value7

Value8

Value9

Value10

Value11

Value12

FILE1

Status

567Q44104

SSSSS

Banking

AAG Txn

DADA

Not Applicable

20726910

SSSSS

AAG Txn

ADG

Accepted

Re: Transpose transformation : SAS DI

LinusH — Mon, 12 Aug 2019 07:39:29 GMT

First, working with the Transpose transformation, with long to wide, is kind of awkward and really not fit for implementation with metadata, since columns are createed dynamically based on input data.

Second, your output data doesn't look like a data ouput, more of a report. IMO that should be taken care of by a repoirting tool, not ETL.

That said, you can't force Transpose to generate the exact layout you want,if your data doen't support it.

Top of my head, I can think of one solution: prepare your input data, so you'll have 12 values for at least one BY group.

Re: Transpose transformation : SAS DI

JJP1 — Mon, 12 Aug 2019 08:59:37 GMT

Thanks @LinusH .Would you please suggest how can we create this input data please.
Currently in my SAS job value are geeting created after sort transformation and then transform transformation where value1,value2...
are created.
now.would you kindly suggest where i can create input data with all value5,value6.. please.

Any other suggestions or advises please

Re: Transpose transformation : SAS DI

LinusH — Mon, 12 Aug 2019 11:27:04 GMT

I guess a preceeeding data step in a User Written transform.

For each by group, restart a counter.

Use a DO-loop to explicitly output additional records with missing values for those by groups that doesn't have 12 records/values (currently all it seems).

Re: Transpose transformation : SAS DI

JJP1 — Mon, 12 Aug 2019 13:47:44 GMT

Thanks .Sorry to trouble you.
Would you please help on code please.kindly have a look at below screen shot of transpose job please.But the value6,value7 ...till value12 data may be coming or may not be coming from source please.

but it should create 12 values unde proc transpose output.if the values are not coming for value6,value7...value12.it should display output as null please.iam trying by taking user written node transformation after sort with below code.but still it is not created desired output but just created based on values only please (value1..value6).but the job should ceate output columns value7..value12 )please.if in case future data will be coming for value6 ..value12 then sas job should be taken care.please advise.

data work.W1B6ODL4;
  set work.W8EDMBU2;

count + 1;
  by NAME VALUE STATUS;
  if first.NAME then count = 1;
  if VALUE then count = 1;
  if STATUS then count = 1;
run;