topic Re: Sorting 40 Date/Disposition Columns Together in SAS Programming

Sorting 40 Date/Disposition Columns Together

PatricktLeon — Fri, 22 Feb 2019 22:29:44 GMT

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!

Re: Sorting 40 Date/Disposition Columns Together

Reeza — Fri, 22 Feb 2019 22:42:10 GMT

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!

Re: Sorting 40 Date/Disposition Columns Together

PatricktLeon — Mon, 25 Feb 2019 16:31:41 GMT

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;