BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PatricktLeon
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!


 

View solution in original post

2 REPLIES 2
Reeza
Super User

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!


 

PatricktLeon
Fluorite | Level 6

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 726 views
  • 0 likes
  • 2 in conversation