Appending data on multiple lines

Posted on 2011-09-29
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
Question by:morinia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 36818434
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?  

Author Comment

ID: 36818519
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.  
LVL 11

Accepted Solution

theartfuldazzler earned 500 total points
ID: 36890027

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;

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.


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>" 

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_;


Open in new window


Author Comment

ID: 36891703
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?

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 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