topic Re: Facing an issue with transpose transformation in SAS DI 4.902 in New SAS User

Facing an issue with transpose transformation in SAS DI 4.902

Lakshmi_G90 — Mon, 01 Apr 2019 07:43:24 GMT

Hi Everyone,

Hope All doing Good !

Here My query is regarding transpose transformation in SAS DI 4.902(9.4 M3).

I am facing issues with duplicate ID values and some times some other warning also I am getting as below.

Below is my code.

PROC TRANSPOSE DATA=WORK.Test
OUT=W2B107UV
NAME=Source
LET
;
BY CompanyId Customer Company;
ID Name;
VAR Value;

RUN; QUIT;

I have tried the same in User written and transpose transformation of DI.

Waning/Error1:

ID value Datetime occurs twice within the same by group.

ID value links occurs twice within the same by group.

So on.

Warning/ Error 2:

Unable to execute query: SQL passthru expression contained these errors: ERROR: Column Source could not be found in the table/view identified with the correlation name W7FYG6DF.... ERROR: Column Datetime could not be found in the table/view identified with the correlation name W7FYG6DF.... ERROR: Column tage3 could not be found in the table/view identified with the correlation name W7FYG6DF.... ERROR: Column title could not be found in the table/view identified with the correlation name W7FYG6DF.... ERROR: Column mainu_Link could not be found in the table/view identified with the correlation name W7FYG6DF.

Sample data of Mine:

CompanyId Customer Company Attribute Value
08768 507689 AB datetime 2018-08-12
971463 791328 BU link https://xxxxx/xxxxxxxx
971463 7913289 AB title Pgigljjguyhihfkjghjh

Please Someone could help Asap..

Earlier response is appreciated.

Many Thanks in Advance!!

Re: Facing an issue with transpose transformation in SAS DI 4.902

Lakshmi_G90 — Mon, 01 Apr 2019 07:50:24 GMT

Sorry, Small correction in the code

PROC TRANSPOSE DATA=WORK.Test
OUT=W2B107UV
NAME=Source
LET
;
BY CompanyId Customer Company;
ID Attribute;
VAR Value;

RUN; QUIT;

Re: Facing an issue with transpose transformation in SAS DI 4.902

Kurt_Bremser — Mon, 01 Apr 2019 08:29:07 GMT

Transpose is not an interactive procedure, so the quit statement is not needed.

And it is what the message says. Within a by group, each value for attribute must appear at most once.

Try the following:

proc sql;
create table counts as select *, count(*) as count
from test
group by CompanyId, Customer, Company, Attribute
having count > 1;
quit;

to detect duplicates

Re: Facing an issue with transpose transformation in SAS DI 4.902

Lakshmi_G90 — Mon, 01 Apr 2019 08:39:26 GMT

@Kurt_Bremser ,Many Thanks for your response.

I have tried the same in data step block by using first.variable statement. Yes, Attribute values occure morethan one time in a group by columns sequence.

But here I need to transpose the data without lossing the records.

Is there a way to get the result..?

Best Regards,

Lakshmi.

Re: Facing an issue with transpose transformation in SAS DI 4.902

Kurt_Bremser — Mon, 01 Apr 2019 08:54:26 GMT

Basic answer: you can't with the current data structure. You may have to use one or more additional columns to get groups where each attribute value occurs only once. It may be that you have to create such a column.

Re: Facing an issue with transpose transformation in SAS DI 4.902

Lakshmi_G90 — Mon, 01 Apr 2019 10:36:07 GMT

@Kurt_Bremser Thank you so much. Now I understood How to go further to fulfil My requirement.