topic Re: how to merge and transpose data table in SAS Programming

how to merge and transpose data table

Kadia — Fri, 17 May 2019 17:47:31 GMT

Hi all,

I wanted to create a constant unique variable n for each ITEM_ID and after that transposing DATA table for having each ITEM_ID with multiple date

see attached table

Thank you for your help

Re: how to merge and transpose data table

VDD — Fri, 17 May 2019 17:53:50 GMT

here is a link to some examples of proc transpose.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

Re: how to merge and transpose data table

Kadia — Fri, 17 May 2019 17:57:12 GMT

Thanks for reply but i need to know how to create unique id then doing the transpose

Re: how to merge and transpose data table

VDD — Fri, 17 May 2019 18:37:54 GMT

what is your item_id is it not unique?

Re: how to merge and transpose data table

ballardw — Fri, 17 May 2019 19:11:22 GMT

@Kadia wrote:

Thanks for reply but i need to know how to create unique id then doing the transpose

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

It is real hard to write code against pictures.

Re: how to merge and transpose data table

Kadia — Fri, 17 May 2019 21:17:01 GMT

It is but as it's not integer it can group correctly

i have this error message ERROR: Data set WORK.QUERY_FOR_ITEM_SMRY_0001 is not sorted in ascending sequence. The current BY group has ITEM_ID =
7262314317311375 and the next BY group has ITEM_ID = 0104617002383014.
NOTE: The SAS System stopped processing this step because of errors.

Re: how to merge and transpose data table

VDD — Fri, 17 May 2019 21:35:21 GMT

preform a proc sort on the information prior to the transpose.

give more information so that we can assist rather than guess.

Re: how to merge and transpose data table

ballardw — Mon, 20 May 2019 20:27:36 GMT

@Kadia wrote:

It is but as it's not integer it can group correctly

i have this error message ERROR: Data set WORK.QUERY_FOR_ITEM_SMRY_0001 is not sorted in ascending sequence. The current BY group has ITEM_ID =
7262314317311375 and the next BY group has ITEM_ID = 0104617002383014.
NOTE: The SAS System stopped processing this step because of errors.

Show entire data step or procedure call along with the error. Copy from the log and paste into a code box opened using the forum's {I} or "running man" icon. The message windows will reformat text reducing the value of the diagnostics that SAS sometimes supplies with errors.

But that is a pretty clear error. You need to sort the data before what ever you were doing. The BY statement in the sort should look the same as the By statement is whatever threw the error.

Re: how to merge and transpose data table

Tom — Mon, 20 May 2019 20:50:52 GMT

Where are you getting those numbers? There is a limit to the number of digits of precision that SAS can store in a number.

507   data _null_;
508   x=constant('exactint');
509   put x= comma30.;
510   run;

x=9,007,199,254,740,992