topic Re: SAS Merge problem in Developers

SAS Merge problem

deleted_user — Fri, 02 Nov 2007 16:37:00 GMT

Hi All,

I have just started programming in SAS and I am stuck at a point where I am not able to see a solution. The problem may be very simple for some and my apologies.

I have following data -

File lookup:
A B
20 1000
20 1001
20 1002
21 1003
21 1004
21 1005
22 1006
22 1007
23 1008
23 1009
23 1010

File Consumer Data:
CID A B
abc 20 1001
abc 20 1002
abc 21 1005
abc 22 1007
xyz 20 1002
xyz 21 1004
xyz 22 1006
xyz 23 1010

The out file should have:

ID A B Flag
abc 20 1000 0
abc 20 1001 1
abc 20 1002 1
abc 21 1003 0
abc 21 1004 0
abc 21 1005 1
abc 22 1006 0
abc 22 1007 1
abc 23 1008 0
abc 23 1009 0
abc 23 1010 0

xyz 20 1000 0
xyz 20 1001 0
xyz 20 1002 1
xyz 21 1003 0
xyz 21 1004 1
xyz 21 1005 0
xyz 22 1006 1
xyz 22 1007 0
xyz 23 1008 0
xyz 23 1009 0
xyz 23 1010 1

So basically, look up would be used like a reference library to merge on for each customer id. And binary 0 or 1 is set against each observation based on the tuple A B chosen by customer.

I can very easily think of solving it in JAVA/C++ but I want to learn a solution in SAS.

Thanks for all the suggestions and help!

Re: SAS Merge problem

Cynthia_sas — Fri, 02 Nov 2007 17:50:43 GMT

Hi:
This question is not directly related to SAS Stored Processes. For help with how to combine or manipulate data, your best bet is to read the documentation on joining SAS data sets or to contact Tech Support for help on this particular problem.

Some papers on the subject are:
http://www2.sas.com/proceedings/sugi28/097-28.pdf
http://www2.sas.com/proceedings/sugi30/061-30.pdf

cynthia

Re: SAS Merge problem

Cynthia_sas — Tue, 06 Nov 2007 17:40:31 GMT

Hi:
It occurs to me that this is very similar to another question on the forum:
http://support.sas.com/forums/thread.jspa?threadID=2097&tstart=0

That person wanted to count people who appeared in a group and you want to do essentially the same thing.

Here's a PROC TABULATE solution that uses a SAS format to do the lookup so you don't have to code it in a MERGE. I coded the format the way I did because it seems that column A is tied to the value in column B and rather than loading 2 formats, it was easier to just load 1 format.

For more help understanding what this program is doing, you might consider reading the PROC TABULATE and PROC FORMAT documentation or contacting Tech Support for help with your particular set of data and output needs. For example, do you need an output table or an output report??? In any case, the Stored Process forum is not the right spot to post questions like this. Beginning syntax or usage questions are most appropriate for Tech Support.

cynthia
[pre]

proc format;
value lookup
1000 = '20 1000'
1001 = '20 1001'
1002 = '20 1002'
1003 = '21 1003'
1004 = '21 1004'
1005 = '21 1005'
1006 = '22 1006'
1007 = '22 1007'
1008 = '23 1008'
1009 = '23 1009'
1010 = '23 1010';
run;

data consumer;
infile datalines;
input CID $ a b;
return;
datalines;
abc 20 1001
abc 20 1002
abc 21 1005
abc 22 1007
xyz 20 1002
xyz 21 1004
xyz 22 1006
xyz 23 1010
;
run;

ods listing;
ods html file='c:\temp\tabexamp.html';

** PROC TABULATE;

options missing=0 nodate nonumber;

proc tabulate data=consumer f=comma8.
formchar='|----|+|--- '
out=work.tabout;
title "Using PROC TABULATE";
class cid;
class b /preloadfmt;
table cid* b, n / printmiss;
label b = 'A and B';
keylabel n = 'Total Category';
format b lookup.;
run;

** show output data created by tabulate;
proc print data=tabout;
title 'proc print of data created by tabulate';
title2 'column "B" uses LOOKUP format';
var cid b n;
run;

ods html close;
[/pre]

and the TABULATE output in the LISTING window would look like this:
[pre]
Using PROC TABULATE

-----------------------------
| | Total |
| |Category|
|------------------+--------|
|CID |A and B | |
|--------+---------| |
|abc |20 1000 | 0|
| |---------+--------|
| |20 1001 | 1|
| |---------+--------|
| |20 1002 | 1|
| |---------+--------|
| |21 1003 | 0|
| |---------+--------|
| |21 1004 | 0|
| |---------+--------|
| |21 1005 | 1|
| |---------+--------|
| |22 1006 | 0|
| |---------+--------|
| |22 1007 | 1|
| |---------+--------|
| |23 1008 | 0|
| |---------+--------|
| |23 1009 | 0|
| |---------+--------|
| |23 1010 | 0|
|--------+---------+--------|
|xyz |20 1000 | 0|
| |---------+--------|
| |20 1001 | 0|
| |---------+--------|
| |20 1002 | 1|
| |---------+--------|
| |21 1003 | 0|
| |---------+--------|
| |21 1004 | 1|
| |---------+--------|
| |21 1005 | 0|
| |---------+--------|
| |22 1006 | 1|
| |---------+--------|
| |22 1007 | 0|
| |---------+--------|
| |23 1008 | 0|
| |---------+--------|
| |23 1009 | 0|
| |---------+--------|
| |23 1010 | 1|
-----------------------------

[/pre]

Re: SAS Merge problem

deleted_user — Tue, 06 Nov 2007 18:24:17 GMT

Thanks a lot for that solution Cynthia!