topic Re: How to output this proc freq result into a data set? in SAS Programming

How to output this proc freq result into a data set?

SASuser4321 — Wed, 07 Dec 2022 10:58:00 GMT

I have the following table with me:

Data1	Data2	Data3
1
3	2
6	3	2
4
5

....

The table has thousands of rows.

I would like to know how many times each number occurs in this table. So for example, for the above table, the output of my code should look like this:

Data_value	Frequency
1	1
2	2
3	2
4	1
5	1
6	1

I tried the following but it didn't work:

data transposed;
set have (Keep=data1 data2 data3);
run;

proc freq data=transposed;
tables data1 data2 data3/out=result;
run;

The last part of the above code, where I tried to output my proc freq result into a data set is the part where I was unsuccessful. Could someone help me output this proc freq result into a data set?

Help would be appreciated. Thank you.

Re: How to output this proc freq result into a data set?

PeterClemmensen — Wed, 07 Dec 2022 11:00:35 GMT

Try this

data have;
input Data1 Data2 Data3;
infile datalines missover;
datalines;
1     
3 2   
6 3 2 
4     
5     
;

data temp(keep = data);
   set have;
   array d Data:;
   do over d;
      data = d;
      if data then output;
   end;
run;

proc freq data = temp noprint;
   tables data / out = want(drop = percent);
run;

Result:

data  COUNT
1     1
2     2
3     2
4     1
5     1
6     1

Re: How to output this proc freq result into a data set?

SASuser4321 — Wed, 07 Dec 2022 11:06:39 GMT

Thank you for your comment. Unfortunately, it didn't work for me. Here is the log source:

1569  data temp(keep = data);
1570     set app_port (Keep=override_reas_1 override_reas_2 override_reas_3);
1571     array d Data:;
WARNING: Defining an array with zero elements.
1572     do over d;
1573        data = d;
1574        if data then output;
1575     end;
1576  run;

NOTE: Compression was disabled for data set WORK.TEMP because compression overhead would increase the size of the data set.
NOTE: There were 8225 observations read from the data set WORK.APP_PORT.
NOTE: The data set WORK.TEMP has 0 observations and 1 variables.

Re: How to output this proc freq result into a data set?

PeterClemmensen — Wed, 07 Dec 2022 11:10:15 GMT

@SASuser4321 is your data representative? Are the names of the columns 'Data1', 'Data2' and so on really Override1 and Override2 and so on?

Re: How to output this proc freq result into a data set?

SASuser4321 — Wed, 07 Dec 2022 11:11:13 GMT

yes, the exact names are override_reas_1 override_reas_2 override_reas_3

Re: How to output this proc freq result into a data set?

PeterClemmensen — Wed, 07 Dec 2022 11:14:13 GMT

Then simply change the names of the variables like this

data have;
input override_reas_1 override_reas_2 override_reas_3;
infile datalines missover;
datalines;
1     
3 2   
6 3 2 
4     
5     
;

data temp(keep = override);
   set have;
   array o override_reas_:;
   do over o;
      override = o;
      if override then output;
   end;
run;

proc freq data = temp noprint;
   tables override / out = want(drop = percent);
run;

Re: How to output this proc freq result into a data set?

PaigeMiller — Wed, 07 Dec 2022 11:15:48 GMT

You need to use the REAL variable names in your code and not the made-up variable names in your original problem.

Better would be to use the REAL variable names in your original problem statement, and this avoids this issue entirely.

Re: How to output this proc freq result into a data set?

SASuser4321 — Wed, 07 Dec 2022 11:17:43 GMT

Thank you. It works now. Could you please explain what each line of the following code does?

array o override_reas_:;
   do over o;
      override = o;
      if override then output;

Re: How to output this proc freq result into a data set?

PeterClemmensen — Wed, 07 Dec 2022 11:20:29 GMT

Great.

Sure thing

array o override_reas_:; /* Create an array of all variables prefixed with oerride_reas_ */
   do over o; /* For each variable in o do.. */
      override = o; /* Set the variable override to the value of the current value of the array */
      if override then output; /* Too avoid missing values in the output data, output only if override is not missing */