Solved: Re: Arrays

V_27 · Posted 10-08-2018 01:44 PM

Hello,

I am new to SAS and I am trying to create an array which can calculate the total count of particular observations value that appears in 3 different variables.
Example: O/P = count of observation value "D80.1" in Var1 + "D80.1" in Var2 + "D80.1" in Var3

Similarly for all other values.

Please find the sample attachment of input data and required output

novinosrin · Posted 10-09-2018 12:09 PM

Thank you @V_27 , I am just curious, if you just want to read the variables you need to compute the counts, I think you should still have your desired counts output.

For example,

/*Reading only the vars you need from have 
i.e rand_id and diag group*/
proc transpose data=have(keep=rand_id diag:) out=w1;
by rand_id;
var diag:;
run;
/*vertical*/
/*This should give you counts for each diag*/
proc freq data=w1(where=(col1 ne '9999')) noprint;
tables col1/out=want(drop=PERCENT);
run;

The above doesn't mess up your original dataset "have" and the "want" has your outputs you need. Am i missing something beyond this?

View solution in original post

novinosrin · Posted 10-08-2018 01:50 PM

I am unable to follow your output count of 29,149 etc for your input

V_27 · Posted 10-08-2018 02:50 PM

Hi Novinosrin,

The attachment is just a overview of the whole data file. There are 1000+ observation. I created this output with a basic logic like below.

if Var1="D80.0" or Var2="D80.0" or Var3="D80.0" then NewVar_D80.0=1;

if Var1="D80.1" or Var2="D80.1" or Var3="D80.1" then NewVar_D80.1=1;

and so on for all such different observation values.

Then I used proc tabulate procedure to get the total number as output.

I want to create an array instead of writing individual if-then statement for all observations.

novinosrin · Posted 10-08-2018 03:06 PM

Not sure if I understood your req well, but here is an attempt

/*k=_n_ a by variable for transpose*/

data have;
k=_n_;
input (Var1	Var2	Var3) ($);
cards;
D80.1	9999	9999
D83.9	D83.0	D80.6
D80.3	9999	9999
D83.9	9999	9999
D82.9	9999	9999
D83.9	9999	9999
D83.9	9999	9999
D83.9	9999	9999
D83.0	9999	9999
D80.6	9999	9999
D83.9	9999	D80.4
D83.8	D89.9	9999
D80.1	9999	9999
D83.9	9999	9999
D83.9	9999	9999
D83.9	9999	9999
D80.1	9999	9999
D80.1	D83.9	D80.4
D83.9	9999	9999
D83.9	9999	9999
D80.1	D83.9	D83.9
D84.9	D80.3	9999
D80.9	9999	9999
D83.9	9999	9999
D83.9	D83.8	9999
D83.8	9999	9999
D80.1	D80.8	D89.9
D80.6	9999	9999
D80.1	9999	9999
D80.1	9999	9999
D83.9	9999	9999
D80.1	9999	9999
D83.9	9999	9999
D83.9	9999	9999
D83.9	D80.3	9999
D83.9	9999	D80.3
D83.9	9999	9999
;


proc transpose data=have out=w1;
by k;
var var:;
run;
/*vertical*/
proc freq data=w1(where=(col1 ne '9999')) noprint;
tables col1/out=w2;
run;

/*hortizontal*/
proc transpose data=w2 out=want;
id col1;
var count;
run;

V_27 · Posted 10-09-2018 10:50 AM

Hi Novinosrin,

Thank you for the help.

This code worked but due to transpose, my other variable observations are repeating which I don't want as its affecting the other results due to increase in total number of observations.

For example: let say there are another variable ID. Then my new data set is changed to shown below

ID0003	Var1	D80.1
ID0003	Var2	9999
ID0003	Var3	9999
ID0004	Var1	D83.9
ID0004	Var2	9999
ID0004	Var3	9999
ID0007	Var1	D80.3
ID0007	Var2	9999
ID0007	Var3	9999
ID0012	Var1	D83.9
ID0012	Var2	9999
ID0012	Var3	9999
ID0019	Var1	D82.9
ID0019	Var2	D83.9
ID0019	Var3	9999
ID0021	Var1	D83.9
ID0021	Var2	9999
ID0021	Var3	9999
ID0022	Var1	D83.9
ID0022	Var2	9999
ID0022	Var3	9999
ID0023	Var1	D83.9
ID0023	Var2	9999
ID0023	Var3	9999
ID0029	Var1	D83.9
ID0029	Var2	9999
ID0029	Var3	9999
ID0031	Var1	D83.0
ID0031	Var2	9999
ID0031	Var3	9999
ID0032	Var1	D80.6
ID0032	Var2	9999
ID0032	Var3	9999
ID0035	Var1	D83.9
ID0035	Var2	9999
ID0035	Var3	9999
ID0036	Var1	D83.8
ID0036	Var2	9999
ID0036	Var3	9999
ID0040	Var1	D80.1
ID0040	Var2	9999
ID0040	Var3	9999
ID0042	Var1	D83.9
ID0042	Var2	9999
ID0042	Var3	9999
ID0043	Var1	D83.9
ID0043	Var2	9999
ID0043	Var3	9999
ID0046	Var1	D83.9
ID0046	Var2	9999
ID0046	Var3	9999
ID0047	Var1	D80.1
ID0047	Var2	D80.2
ID0047	Var3	D80.9
ID0049	Var1	D80.1
ID0049	Var2	9999
ID0049	Var3	9999
ID0053	Var1	D83.9
ID0053	Var2	9999
ID0053	Var3	9999
ID0056	Var1	D83.9
ID0056	Var2	9999
ID0056	Var3	9999
ID0058	Var1	D83.9
ID0058	Var2	9999
ID0058	Var3	9999
ID0060	Var1	D80.1
ID0060	Var2	9999
ID0060	Var3	9999
ID0061	Var1	D84.9
ID0061	Var2	9999
ID0061	Var3	9999
ID0062	Var1	D80.9
ID0062	Var2	9999
ID0062	Var3	9999
ID0063	Var1	D83.9
ID0063	Var2	9999
ID0063	Var3	9999
ID0066	Var1	D83.9
ID0066	Var2	9999
ID0066	Var3	9999
ID0068	Var1	D83.8
ID0068	Var2	9999
ID0068	Var3	9999
ID0074	Var1	D80.1
ID0074	Var2	9999
ID0074	Var3	9999
ID0077	Var1	D80.6
ID0077	Var2	9999
ID0077	Var3	9999
ID0079	Var1	D80.1
ID0079	Var2	9999
ID0079	Var3	9999
ID0080	Var1	D80.1
ID0080	Var2	9999
ID0080	Var3	9999
ID0087	Var1	D83.9
ID0087	Var2	9999
ID0087	Var3	9999
ID0089	Var1	D80.1
ID0089	Var2	9999
ID0089	Var3	9999
ID0093	Var1	D83.9
ID0093	Var2	9999
ID0093	Var3	9999
ID0095	Var1	D83.9
ID0095	Var2	9999
ID0095	Var3	9999
ID0099	Var1	D83.9
ID0099	Var2	9999
ID0099	Var3	9999
ID0100	Var1	D83.9
ID0100	Var2	D83.8
ID0100	Var3	9999
ID0101	Var1	D83.9
ID0101	Var2	9999
ID0101	Var3	9999
ID0102	Var1	D83.9
ID0102	Var2	9999
ID0102	Var3	9999
ID0103	Var1	D80.1
ID0103	Var2	D83.0
ID0103	Var3	9999
ID0116	Var1	D83.0
ID0116	Var2	9999
ID0116	Var3	9999
ID0117	Var1	D83.8
ID0117	Var2	9999
ID0117	Var3	9999
ID0118	Var1	D80.6
ID0118	Var2	9999
ID0118	Var3	9999
ID0122	Var1	D83.9
ID0122	Var2	9999
ID0122	Var3	9999
ID0123	Var1	D83.9
ID0123	Var2	9999
ID0123	Var3	9999
ID0127	Var1	D83.9
ID0127	Var2	9999
ID0127	Var3	9999
ID0128	Var1	D83.9
ID0128	Var2	9999
ID0128	Var3	9999
ID0134	Var1	D83.9
ID0134	Var2	9999
ID0134	Var3	9999
ID0139	Var1	D83.9
ID0139	Var2	9999
ID0139	Var3	9999
ID0140	Var1	D83.9
ID0140	Var2	9999
ID0140	Var3	9999

Is there any way I can avoid this situation? As I was also trying to use transpose only before but I can't figure out how to avoid other repeating values.
Thanks in advance again.

novinosrin · Posted 10-09-2018 10:58 AM

Can you please post a good representative sample for us so that we can avoid going back and forth? That will help us test better with the right input sample.

V_27 · Posted 10-09-2018 11:06 AM

Attached is a part of the original dataset

novinosrin · Posted 10-09-2018 12:09 PM

Thank you @V_27 , I am just curious, if you just want to read the variables you need to compute the counts, I think you should still have your desired counts output.

For example,

/*Reading only the vars you need from have 
i.e rand_id and diag group*/
proc transpose data=have(keep=rand_id diag:) out=w1;
by rand_id;
var diag:;
run;
/*vertical*/
/*This should give you counts for each diag*/
proc freq data=w1(where=(col1 ne '9999')) noprint;
tables col1/out=want(drop=PERCENT);
run;

The above doesn't mess up your original dataset "have" and the "want" has your outputs you need. Am i missing something beyond this?

V_27 · Posted 10-09-2018 03:30 PM

I understood what you were saying. The missing part was, you created new dataset "w1" after transpose which does not included variable infusion_1-infusion_4 and I was also asked to create another output table(apart from the output I asked in the question) which contains total diag count during different number of infusions which I was unable to as dataset w1 has repeated rand_id due to transpose.

But in the end I figured out. I used proc transpose method only and merged the new transposed dataset to original one by rand_id which I can use to generate different output tables with different variables.

Thank you so much for your help. 🙂

Is there a way I can get in touch with you? @novinosrin

novinosrin · Posted 10-09-2018 03:57 PM

I am glad, you got it to work. Feel free to open up any number of threads/questions. We(you included) are all part of the SAS family. Don't ever hesitate. The pleasure would all me mine to help. Of course, this is the best platform for all us to share and learn. I try to get here almost everyday. 🙂

PS IF anything urgent, besides your open thread here, PM me. I will try to look into it for sure. Nevertheless, on the open thread you are bound to get responses from champs like John King, Pierre Gagnon, Tom, Paul Dorfman, Xia keshan, Mkeintz, Art T etc with their super answers. These people are synonymous with this gorgeous language of SAS. I tend to copy theirs. haha

Tom · Posted 10-08-2018 01:51 PM

If it appears in two of the variables on the same observation does that count as one or two?

V_27 · Posted 10-08-2018 02:51 PM

Hi Tom,

Yes, it should count as two.

Tom · Posted 10-08-2018 03:09 PM

So just transpose the data so that you have one column and run frequencies on that.

Here is one method using an ARRAY, but you should also look into just using PROC TRANSPOSE.

data for_analysis;
  set have ;
  array dxlist var1-var3 ;
  do i=1 to dim(dxlist);
    dx=dxlist(i);
    output;
  end;
run;

proc freq data=for_analysis;
  tables dx;
run;

V_27 · Posted 10-09-2018 10:54 AM

Hi Tom,

Thanks for the solution.

I already tried this code before but the problem is my other variable observations are repeating which I don't want as its affecting the other results due to increase in total number of observations like I said to Novinosrin.

For example: let say there is another variable ID. Then my new data set is changed to shown below

ID0003	Var1	D80.1
ID0003	Var2	9999
ID0003	Var3	9999
ID0004	Var1	D83.9
ID0004	Var2	9999
ID0004	Var3	9999
ID0007	Var1	D80.3
ID0007	Var2	9999
ID0007	Var3	9999
ID0012	Var1	D83.9
ID0012	Var2	9999
ID0012	Var3	9999
ID0019	Var1	D82.9
ID0019	Var2	D83.9
ID0019	Var3	9999
ID0021	Var1	D83.9
ID0021	Var2	9999
ID0021	Var3	9999
ID0022	Var1	D83.9
ID0022	Var2	9999
ID0022	Var3	9999
ID0023	Var1	D83.9
ID0023	Var2	9999
ID0023	Var3	9999
ID0029	Var1	D83.9
ID0029	Var2	9999
ID0029	Var3	9999
ID0031	Var1	D83.0
ID0031	Var2	9999
ID0031	Var3	9999
ID0032	Var1	D80.6
ID0032	Var2	9999
ID0032	Var3	9999
ID0035	Var1	D83.9
ID0035	Var2	9999
ID0035	Var3	9999
ID0036	Var1	D83.8
ID0036	Var2	9999
ID0036	Var3	9999
ID0040	Var1	D80.1
ID0040	Var2	9999
ID0040	Var3	9999
ID0042	Var1	D83.9
ID0042	Var2	9999
ID0042	Var3	9999
ID0043	Var1	D83.9
ID0043	Var2	9999
ID0043	Var3	9999
ID0046	Var1	D83.9
ID0046	Var2	9999
ID0046	Var3	9999
ID0047	Var1	D80.1
ID0047	Var2	D80.2
ID0047	Var3	D80.9
ID0049	Var1	D80.1
ID0049	Var2	9999
ID0049	Var3	9999
ID0053	Var1	D83.9
ID0053	Var2	9999
ID0053	Var3	9999
ID0056	Var1	D83.9
ID0056	Var2	9999
ID0056	Var3	9999
ID0058	Var1	D83.9
ID0058	Var2	9999
ID0058	Var3	9999
ID0060	Var1	D80.1
ID0060	Var2	9999
ID0060	Var3	9999
ID0061	Var1	D84.9
ID0061	Var2	9999
ID0061	Var3	9999
ID0062	Var1	D80.9
ID0062	Var2	9999
ID0062	Var3	9999
ID0063	Var1	D83.9
ID0063	Var2	9999
ID0063	Var3	9999
ID0066	Var1	D83.9
ID0066	Var2	9999
ID0066	Var3	9999
ID0068	Var1	D83.8
ID0068	Var2	9999
ID0068	Var3	9999
ID0074	Var1	D80.1
ID0074	Var2	9999
ID0074	Var3	9999
ID0077	Var1	D80.6
ID0077	Var2	9999
ID0077	Var3	9999
ID0079	Var1	D80.1
ID0079	Var2	9999
ID0079	Var3	9999
ID0080	Var1	D80.1
ID0080	Var2	9999
ID0080	Var3	9999
ID0087	Var1	D83.9
ID0087	Var2	9999
ID0087	Var3	9999
ID0089	Var1	D80.1
ID0089	Var2	9999
ID0089	Var3	9999
ID0093	Var1	D83.9
ID0093	Var2	9999
ID0093	Var3	9999
ID0095	Var1	D83.9
ID0095	Var2	9999
ID0095	Var3	9999
ID0099	Var1	D83.9
ID0099	Var2	9999
ID0099	Var3	9999
ID0100	Var1	D83.9
ID0100	Var2	D83.8
ID0100	Var3	9999
ID0101	Var1	D83.9
ID0101	Var2	9999
ID0101	Var3	9999
ID0102	Var1	D83.9
ID0102	Var2	9999
ID0102	Var3	9999
ID0103	Var1	D80.1
ID0103	Var2	D83.0
ID0103	Var3	9999
ID0116	Var1	D83.0
ID0116	Var2	9999
ID0116	Var3	9999
ID0117	Var1	D83.8
ID0117	Var2	9999
ID0117	Var3	9999
ID0118	Var1	D80.6
ID0118	Var2	9999
ID0118	Var3	9999
ID0122	Var1	D83.9
ID0122	Var2	9999
ID0122	Var3	9999
ID0123	Var1	D83.9
ID0123	Var2	9999
ID0123	Var3	9999
ID0127	Var1	D83.9
ID0127	Var2	9999
ID0127	Var3	9999
ID0128	Var1	D83.9
ID0128	Var2	9999
ID0128	Var3	9999
ID0134	Var1	D83.9
ID0134	Var2	9999
ID0134	Var3	9999
ID0139	Var1	D83.9
ID0139	Var2	9999
ID0139	Var3	9999
ID0140	Var1	D83.9
ID0140	Var2	9999
ID0140	Var3	9999

Is there any way I can avoid this situation? As I was also trying to use transpose only before but I can't figure out how to avoid other repeating values.
Thanks again.

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away