topic Re: Transpost in DI Studio in SAS Data Management

Transpose in DI Studio

crawfe — Wed, 10 Jun 2020 12:54:59 GMT

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;

Re: Transpost in DI Studio

s_lassen — Wed, 03 Jun 2020 18:20:11 GMT

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?

Re: Transpost in DI Studio

crawfe — Wed, 03 Jun 2020 19:06:40 GMT

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?

Re: Transpost in DI Studio

DavePrinsloo — Wed, 03 Jun 2020 19:41:19 GMT

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

Re: Transpost in DI Studio

s_lassen — Thu, 04 Jun 2020 05:39:50 GMT

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.

Re: Transpost in DI Studio

Patrick — Thu, 04 Jun 2020 05:52:00 GMT

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

Re: Transpose in DI Studio

crawfe — Fri, 05 Jun 2020 17:07:11 GMT

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).