BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASuser4321
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

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
SASuser4321
Obsidian | Level 7

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.
PeterClemmensen
Tourmaline | Level 20

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

SASuser4321
Obsidian | Level 7
yes, the exact names are override_reas_1 override_reas_2 override_reas_3
PeterClemmensen
Tourmaline | Level 20

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;
SASuser4321
Obsidian | Level 7

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;
PeterClemmensen
Tourmaline | Level 20

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 */
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 558 views
  • 2 likes
  • 3 in conversation