Facing an issue with transpose transformation in SAS DI 4.902

[Lakshmi_G90]

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!! 

[Lakshmi_G90]

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;

[Kurt_Bremser]

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

[Lakshmi_G90]

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

[Kurt_Bremser · Accepted solution]

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.

[Lakshmi_G90]

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