combine datasets even when results are missing

[SAS_Question]

/* have COURSES-dataset */
data have_course;
    input PLAN COURSE_ID $;
    datalines;
1001 CRS_101
1001 CRS_102
1001 CRS_103
1002 CRS_201
1002 CRS_202
1002 CRS_203
1003 CRS_101
1003 CRS_301
1003 CRS_302
1003 CRS_303
;

/* have STUDENT-dataset */
data have_student;
    input ID $ COURSE_ID $ RESULT PLAN;
    datalines;
001 CRS_101 7 1001
001 CRS_102 6 1001
001 CRS_103 5 1001
001 CRS_103 6 1001
001 CRS_201 7 1002
001 CRS_202 3 1002
001 CRS_202 5 1002
001 CRS_203 6 1002
002 CRS_101 8 1001
002 CRS_102 7 1001
002 CRS_103 6 1001
002 CRS_201 9 1002
002 CRS_202 8 1002
002 CRS_203 4 1002
002 CRS_203 5 1002
003 CRS_103 6 1001
003 CRS_201 9 1002
;

/* Sort COURES-dataset on PLAN */
proc sort data=have_course;
    by PLAN COURSE_ID;
run;

/* Sort STUDENT-dataset on PLAN */
proc sort data=have_student;
    by PLAN COURSE_ID ID;
run;

/* create course list datasets based on plan */
data _null_;
    set have_course;
    by PLAN COURSE_ID;
    call execute(cats(
        'data CRS_SPL_', plan, '; ',
        'set have_course;',
        'if plan = ', plan, ';',
        'run;'
    ));
run;


/* what i want */

I want in each (seperated) dataset, which is called/begins with: CRS_SPL_* :
-I want every student which had a result for that plan in it
with every crs_* listed even if that student had no result for that course.

I hope I made it clear..if not, i hope the screenshot of what I want, clears this very much.
If not, please ask. I will try to clarify this more than. Thank you very much in advance!

So in Excel it will look like this, exactly what I want to achieve in SAS:
see attached picture

Please do ignore the coloring ..i colored the tables just to highlight the similarities/differences. Thank you! 

[ErikLund_Jensen · Accepted solution]

Hi @SAS_Question 

The following code creates the wanted output. I must admit that I could not follow your line of reasoning behind the data _null_ step, so I made it "my own way".  The code should be able to handle more complex input, like more results to put in different columns.  

/* have COURSES-dataset */
data have_course;
    input PLAN COURSE_ID $;
    datalines;
1001 CRS_101
1001 CRS_102
1001 CRS_103
1002 CRS_201
1002 CRS_202
1002 CRS_203
1003 CRS_101
1003 CRS_301
1003 CRS_302
1003 CRS_303
;

/* have STUDENT-dataset */
data have_student;
    input ID $ COURSE_ID $ RESULT PLAN;
    datalines;
001 CRS_101 7 1001
001 CRS_102 6 1001
001 CRS_103 5 1001
001 CRS_103 6 1001
001 CRS_201 7 1002
001 CRS_202 3 1002
001 CRS_202 5 1002
001 CRS_203 6 1002
002 CRS_101 8 1001
002 CRS_102 7 1001
002 CRS_103 6 1001
002 CRS_201 9 1002
002 CRS_202 8 1002
002 CRS_203 4 1002
002 CRS_203 5 1002
003 CRS_103 6 1001
003 CRS_201 9 1002
;
/* PREPARE INPUT */
* Multiply records in have_course by ID;
proc sql;
  create table plan_exp as
    select a.PLAN, a.COURSE_ID, b.ID
    from have_course as a, (select distinct ID from have_student) as b
    order by a.PLAN, b.ID, a.COURSE_ID;
quit;

* Sort STUDENT-dataset on PLAN;
proc sort data=have_student;
    by PLAN ID COURSE_ID;
run;

* Merge to get all plans + results in same dataset;
data w1;
  merge plan_exp have_student;
  by PLAN ID COURSE_ID;
run;

* Transpose have_student - results in columns instead of rows;
proc transpose data=w1 prefix=RESULT_ out=w2 (drop=_NAME_);
  by PLAN ID COURSE_ID;
run;

/* CREATE OUTPUT */
* Build list and count of plans;
proc sql noprint;
  select distinct cats('CRS_SPL_', PLAN) 
  into :dslist separated by '/'
  from w2;
quit;
%put &=dslist;
%put &=sqlobs;

* Split in separate datasets;
* Note the length statement before the set statement is to bring variables in correct order;
%macro m;
  %do i = 1 %to &sqlobs;
    %let thisds = %scan(&dslist,&i,/);
    %let thisplan = %scan(&thisds,3,_);
    data &thisds;
      length PLAN 8 COURSE_ID $8 ID $8;
      set w2 (where=(PLAN=input("&thisplan",8.)));
    run;
  %end;
%mend;
%m;

[SAS_Question]

---

@ErikLund_Jensen wrote:

Hi @SAS_Question 

The following code creates the wanted output. I must admit that I could not follow your line of reasoning behind the data _null_ step, so I made it "my own way".  The code should be able to handle more complex input, like more results to put in different columns.  

Firstly, thank you very much for this simple solution and very quick response! Thank you very much!

As for your remark ("I must admit that I could not follow your line of reasoning behind the data _null_ step"), dear @ErikLund_Jensen, I was going the wrong way with my code and completely blocked ... but this way of your code is neat and quick of you... saved my day! 
I really like the way you did that with PROC SQL!  

[Tom]

This statement seems confused.

set w2 (where=(PLAN=input("&thisplan",8.)));

If THISPLAN has a string that looks like a number you can just do

set w2 (where=(PLAN=&thisplan));

And if it doesn't then PLAN should be a character variable so you should use:

set w2 (where=(PLAN="&thisplan"));

Note also that if THISPLAN is a number that is more than 8 digits long using the 8. informat will only read the first 8 digits.  If you you want to use the INPUT() function the just use the maximum width of 32.

[ErikLund_Jensen]

Hi @Tom , @SAS_Question 

You are right, of course.  I used double quotes because I thought PLAN was a character variable, and then just added the input function to get it to work instead of removing the quotes. Not exactly elegant.