Hello SAS Community! I have a spreadsheet with 80 columns that are date and call disposition combinations. Basically the data came in from multiple sources, so each row is a mashup of the various data sources. For each row, there are various sections of these date and call disposition columns that are blank. The format is Date Column, Disposition Column, Date Column, Disposition Column, etc. My question is, how can I sort this by date for each row, and keep the disposition column next to the correct date? Any tips would be greatly appreciated, thanks in advance!
Attached is a sample of the data structure, shortened to 16 date/disposition columns to illustrate the task at hand, thanks!
Change your data structure to a long format. Sort together and then flip it back wide if you need to. .
Depending on how many levels you have you may want:
ID Source DispNo DispReason Date
1 1 1 Wrong Number 20Nov2018
1 1 2 No Answer 12Dec2018
It's probably easiest to flip the data using an array method.
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
You'll likely also want the VNAME() function -returns name of a variable in an array.
As well as SCAN() to separate the components or COMPRESS(variable, , 'kd') to extract only digits from a text value.
@PatricktLeon wrote:
Hello SAS Community! I have a spreadsheet with 80 columns that are date and call disposition combinations. Basically the data came in from multiple sources, so each row is a mashup of the various data sources. For each row, there are various sections of these date and call disposition columns that are blank. The format is Date Column, Disposition Column, Date Column, Disposition Column, etc. My question is, how can I sort this by date for each row, and keep the disposition column next to the correct date? Any tips would be greatly appreciated, thanks in advance!
Attached is a sample of the data structure, shortened to 16 date/disposition columns to illustrate the task at hand, thanks!
Change your data structure to a long format. Sort together and then flip it back wide if you need to. .
Depending on how many levels you have you may want:
ID Source DispNo DispReason Date
1 1 1 Wrong Number 20Nov2018
1 1 2 No Answer 12Dec2018
It's probably easiest to flip the data using an array method.
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
You'll likely also want the VNAME() function -returns name of a variable in an array.
As well as SCAN() to separate the components or COMPRESS(variable, , 'kd') to extract only digits from a text value.
@PatricktLeon wrote:
Hello SAS Community! I have a spreadsheet with 80 columns that are date and call disposition combinations. Basically the data came in from multiple sources, so each row is a mashup of the various data sources. For each row, there are various sections of these date and call disposition columns that are blank. The format is Date Column, Disposition Column, Date Column, Disposition Column, etc. My question is, how can I sort this by date for each row, and keep the disposition column next to the correct date? Any tips would be greatly appreciated, thanks in advance!
Attached is a sample of the data structure, shortened to 16 date/disposition columns to illustrate the task at hand, thanks!
Hi Reeza, thank you for helping me visualize the approach! I ended up writing a macro loop to flip the data:
%macro sorted;
%local i;
PROC SQL NOPRINT;
SELECT DISTINCT (dates) into :date1- :date40
FROM dates;
QUIT;
%do i = 1 %to &sqlobs;
DATA DATES&i(KEEP=ID Date Disp);
SET DECEMBER_SORT;
FORMAT Date date9.;
Date = Date&i;
Disp = Disp&i;
RUN;
%end;
%mend sorted;
%sorted;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.