Retain Statement vs Array statement

[Astounding]

Sorry, my mistake.  Your variable name is actually resort_id and not resort.  I changed the original post to include the corrections.

[CamRutherford]

Any ideas how I can do the below?

I have a dataset in SAS that looks like the below but I'm looking to do the following and think an Array statement is the best way to do so...

My current data

SBR_ID	Value
1234	A
1234	B
5432	A
6789	A
5432	C
1234	B
5432	B
6789	A
6789	C

I want my data set to end up like this whereby for each SBR_ID it will sum the amount of times that value appears. 

SBR_ID	A	B	C
1234	1	2	
5432	1	1	1
6789	2	0	1

Thanks 

[Astounding]

 I can't test this right now, but this should be a step in the right direction:

proc tabulate data=have;

class sbr_id value;

tables sbr_id, value=' ';

run;

This generates a report, although it should be easy enough to add options that create a data set as the output.

[Tommywhosc]

Hi Cam,

It appears you're reading in 15 variables for up to 5 rows per each SBR_ID, and creating 75 variables.

If that's correct, and you want to use arrays, you can try this (un-tested) code:

Keep your Retain, Format  statements, and then  create 2 arrays: ALL and RES:

Array          all {75} $

RESORT1 RESORT2 RESORT3 RESORT4 RESORT5

R1_UNIT R2_UNIT R3_UNIT R4_UNIT R5_UNIT                                                                                                                          

R1_OWN_DATE R2_OWN_DATE R3_OWN_DATE R4_OWN_DATE R5_OWN_DATE

R1_BED R2_BED R3_BED R4_BED R5_BED ….. ;  *The 75 vars you are creating  ;

Array          Res {15} Resort_ID Unit_No Own_Purch_Dt .. ;  *the 15 vars being read  ;

/* Make sure the 15 variables in array RES match up 1-to-1 with each “15 subset” of array ALL */

By SBR_ID;

If First.SBR_ID then DO;

                    Do k = 1 to 75;

                                         All{k} = ‘ ‘;

                    End;

Flag=1;

End;

/* IF Flag > 5 then delete;  */  *Here, a check in case you have >5 rows/SBR_ID  ;

All_Stop = Flag*15;

All_Start = All_Stop – 14;

Do I = All_Start to All_Stop by 1;    *I goes from 1 to 75, in runs of 15, for each FLAG = 1,2,..5  ;

                    J = I – (Flag-1)*15;      *J goes from 1 to 15    ;

                    All{i} = Res{j};

End;

Flag + 1;

If last.SBR_ID;      /* I'm assuming you don't want all 5 rows per SBR_ID  saved */

/* Drop Flag, All_start, All_Stop, i,j,k etc...   */

Run;