Transpose in DI Studio

[crawfe]

Hi, I am trying to transpose daily data using UserWrittenCode in DI Studio

which I will append each day to a master table.

HAVE (data):

DATE             STATUS        COUNT_1

06/01/2020     Done              4

06/01/2020     Start               2

06/01/2020     IP                   1

06/01/2020     FAIL               7

WANT (test1):

Date                 Done        Start       IP       FAIL

06/01/2020          4              2           1          7

Can you see an error in this code? Thanks!

%let etls_tableExists = %eval(%sysfunc(exist(&_OUTPUT, DATA)));
%if (&etls_tableExists) %then %do;
proc sql noprint;
drop table &_OUTPUT.;
quit;
%end;

PROC TRANSPOSE data=&_INPUT out=test1;
by DATE;
id STATUS;
var COUNT_1;
RUN;

DATA &_OUTPUT. ;
SET test1;
run;

[s_lassen]

Sorry, I can't really see any errors in your code. The only way the output from PROC TRANSPOSE differs from your WANT data is that it has a _NAME_ variable, which you may want to drop. Other than that, no. 

What is your problem with the code?

[crawfe]

Thanks. The code runs but gives an "error fetching data - ...table not exist". The temp table from the previous step does exist.

The incoming table has columns DATE, STATUS and COUNT_1. What should the incoming-outgoing column mapping look like? Would an error there give this error message?

[DavePrinsloo]

As often suggested here, please post the log of the previous step that creates the input table and the step containing the error.

[s_lassen]

You would not have happened to have the same input as output? In that case you are deleting the input before trying to read it. If you move the deletion code down to execute after the transpose code, that may help.

[Patrick · Accepted solution]

Is there a reason why you need user written code and can't use the DIS Transpose transformation?

[crawfe]

I was frustrated with the transform so I was trying the code. I did get the Transpose transform to work eventually. It is very demanding.

For future reference: Each output column needs to match and have the exact spelling from the input row names (not surprising). My problem was that the transform was automatically creating an output table name with an underscore; if you didn't select the option to allow for special characters, in the output table properties, it wouldn't work (should be built-in IMO).