Cartesian in datastep?

[wphupkes]

Hi all,

I've got the following problem:
My customers can be provided with serveral profiles. These profiles, ofcourse, can change in time per customer. In my database this is stored as "Customer has changed from 'Profile 21' to 'Profile 2'". These changes are recorded with a date on the same row (and ofcourse a customer ID).

What I want is a big table with every possible profile change (for the sake of reporting). Do I have to make 2 datasets with all the profiles in it, like:

Dataset_1:
Customer has changed from 'Profile 1'
Customer has changed from 'Profile 2'
Customer has changed from 'Profile 3'
Customer has changed from 'Profile 4'
etc.

Dataset_2:
to 'Profile 1'
to 'Profile 2'
to 'Profile 3'
to 'Profile 4'

and make a cartesian? (I've tried this, but I can't create a cartesian product in a datastep??) or is there a more simple way (which I prefer), like working with arrays (2 sets), and then join these sets in a do-loop with the 'Customer has changed' and 'to' added to these variables?

Thanks in advance for your answer!!

 

[kdt82]

SQL is ideal for this situation as it does cartesian joins by default. The datastep, with its sequential accessing of records doesn't lend itself very well to this type of join, although it is possible with loops.

If you do a self join (joining a dataset with itself by giving the same dataset different aliases) this is quite straightforward.

The where clause here is just to filter out records that match with themselves.

Also bear in mind that proc sql is not weakly typed like the datastep (i.e you will need to explicitly convert numeric to character data with put/input functions). HTH

data profile_change;
input profile $1;
cards;
1
2
3
4
;
run;

proc sql;
create table cartesian as 
select cat('Customer has changed from Profile',a.profile,
            ' to profile',b.profile) as all_types
       from profile_change as a, profile_change as b
       where a.profile ne b.profile;
quit;

 

[ChrisW75]

You can't do a cartesian in a datastep. You need to use proc sql.

proc sql;
  create table C as
  select A.*
        ,B.field2
        ...
  from table_A  A
   left join
       table_B  B
    on A.custID = B.custID
  ;
quit;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[wphupkes]

Thanks! I tried for long time to make it work within a datastep like below, but yes, SQL will make life easier for this...

data a;
do while not ( end_of_b );
set b end = end_of_b;
end_of_c = 0;
do while not ( end_of_c );
set c end = end_of_c;
output;
end;
end;
stop;
run;