Solved

Appending data on multiple lines

Posted on 2011-09-29
4
335 Views
Last Modified: 2013-11-16
I don't know if this is possible in SAS, but this is what I would like to do.
I have a member   00001  Doe, John
John Doe has information coming from multiple tables, ie. claims;drugs;events
The number of rows coming from each table can vary.
Is there a way to format a report so the end result look like this: Multiple steps are fine
  ID       LN     FN        Claims         Drugs        Events
00001  Doe  John     claim 1        drug 1        event 1
                                 claim 2        drug 2        event 2
                                                    drug 3        event  3
                                                    drug 4
                                                    drug 5
00002  Smith Mary    claim 1                 drug1           event1

I dont' want data repeated if possible
0
Comment
Question by:morinia
  • 2
4 Comments
 
LVL 7

Expert Comment

by:d507201
Comment Utility
Reporting is the easy part.  I'm not very much of a proc report user so I'd do it with a PUT statement.

The trick is to get the reporting data set together properly.  

--Does each of the source tables contain the variable ID so we can use that as the merge key?
--How are the claims, drugs and event tables joined if each has multiple records for an ID?  Is there a claim_ID also?  How do I know that drug 3 goes with claim 2?  How do I line up an event with a drug?
--What variables does each table hold?  For example, from which table does drug 1 come from-- claims or drugs?  
0
 

Author Comment

by:morinia
Comment Utility
Each table can be joined on member_id. The tables are not joined as yet, that is the question.  If I do a regular join, I will repetitive data, that is what I am trying to avoid.

There are three different tables.  One table has claims information, another table has drug information and another table has event information all for the respective member.

I am trying to concatenate the information from each table to the respective member.  In the example above member 00001 has two records on the claims table, five records on the drugs table and three records on the event table.

Ideally I want only five lines for member 00001.  The column claims will have two lines populated and three empty lines.  The column drugs will have five lines populated. The column events will have three lines populated and two lines blank.

All three tables have a common variable member_id to join them.  
0
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 500 total points
Comment Utility
Hi

SAS DATA Step with a BY statement will probably do this well...  You would need to have all three tables sorted by member_id before the datasetp (using PROC SORT or similar)

DATA All_merged;
  SET Claims Drug Event;
BY member_ID;
RUN;


The end result should look like:

ID       LN     FN        Claims         Drugs        Events
00001  Doe  John     claim 1        drug 1        event 1
00001  Doe  John     claim 2       drug 2        event 2
00001  Doe  John     claim 2       drug 3        event  3
00001  Doe  John     claim 2       drug 4        event  3
00001  Doe  John     claim 2       drug 5        event  3
00002  Smith Mary    claim 1        drug1           event1

As you can see - SAS repeats the last value of a column if it is missing.

To do the report - I've used the following datastep to output to the LOG and an HTML doc.




As you can see, SAS just repeats the last variable when it is missing.

There are a couple options for reporting.  Using the PUT statement is probably easist.  Note, I have created 'New_' variables and used a LAG fuinction so avoid repeating values.

     

 
OPTIONS LS=255;
ODS HTML FILE="&work.\_____.htm" 
  headtext="<style>.text {mso-number-format:\@}</style><style>.comma {mso-number-format:\#\,\#\#0\.00}</style><style>.commaz {mso-number-format:\#\,\#\#0}</style><style> td {border:0.5pt}</style>" 
  STYLE=Minimal;



DATA _null_;
 set all_merged;
 by member_id;

 format new_ID $6.      New_LN     New_FN        New_Claims         New_Drugs        New_Events $20.;

 if member_id = lag(member_id) then new_id = ''; else new_ID = member_id;
 if LN = lag(LN) then new_LN = ''; else new_LN = LN;
 if FN = lag(FN) then new_FN = '';else new_FN = FN;
 if Claims = lag(Claims) then new_Claims = ''; else new_Claims = Claims;
 if Drugs = lag(Drugs) then new_Drugs = '';else new_Drugs = Drugs;
 if Events = lag(Events) then new_Events = ''; else new_Events = Events;

/* Output to LOG */

 IF _N_ = 1 THEN PUT  'ID' +4 'LN' +19 'FN' +19 'Claims' +15 'Drugs' +16 'Events' overprint @1 110*'-';

 PUT  new_id 5. +1  new_LN $20.  +1 new_FN $20. +1 new_Claims $20. +1 new_Drugs $20. +1 new_Events $20.;


/* Output to HTML */
 file print ods=(variables=(new_ID New_LN New_FN New_Claims New_Drugs New_Events));
title 'Listing of customers';
label new_ID = 'ID' new_LN = 'Last Name' new_FN = 'First Name' new_Claims = 'Claims' New_drugs = 'Drugs' new_events = 'Events';
put _ods_;



RUN;
ODS HTML CLOSE;

Open in new window

0
 

Author Comment

by:morinia
Comment Utility
WOW.  Thanks a lot.  I  am working on putting this in my code.  

Am I correct that you gave two types of output?  One is a report and the other HTML?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now