set statement

[dustychair]

Hi all,

I am sure there is an easy way to do it but you can see my looong code for a simple proc tabulate. By using this code I am calculating some descriptive statistics for each domain (i.e. listening reading etc.). In the end, I want to put all of them together. So, I'm saving them in my drive then importing (because it is not saving them as a SAS file in the SAS.) them and trying to put them together. The problem is it's only putting the "overall" results which is the last domain in the macro. It's overwriting. How can resolve it?

Thanks,

ods excel file="C:\Grade\scale_scores\ss_by_gr_&s..xlsx" style=minimal;
proc tabulate data=alt_&s. out=ss_by_gr_&s. ;
class grade;
var &s._scale_score;
table &s._scale_score*(format=comma16.) ,
grade *(N* format=comma16. Mean* f=6.2 Std*f=6.2);
run;
ods excel close;
PROC IMPORT OUT= ss_by_gr_&s. DATAFILE= "C:\Grade\scale_scores\ss_by_gr_&s..xlsx"
DBMS=xlsx REPLACE;
GETNAMES=YES;
RUN;

data ss_by_gr_all;
set ss_by_gr_&s. ;
run;

%mend;
%part (listening);
%part (reading);
%part (speaking);
%part (writing);
%part (oral);
%part (literacy);
%part (comprehension);
%part (overall);

[ballardw]

Since your apparent problem is with what the macro is creating then perhaps you should provide the code for the macro.

This statement:

So, I'm saving them in my drive then importing (because it is not saving them as a SAS file in the SAS.)

makes me strongly believe that you are concerned with output in the RESULTS window. Most procedures have a way to create output data sets that you likely just have not learned to use yet. I would not be surprised to find that importing Proc Tabulate output  has issues with creating a "nice" data set because of the nesting of things like variable names, values and statistic names. Your example:

table &s._scale_score*(format=comma16.) ,
grade *(N* format=comma16. Mean* f=6.2 Std*f=6.2);

will make a header that consists of 1) the variable name that spans 2) each value of grade and 3) (third row) 2 statistics, N and Mean. So you have many columns that do not have "column header" because of the spanning grade.

The comment about only have the "overall" output makes me suspect that you are reusing something that gets overwritten by each macro call. Without the code for the macro its hard to say exactly where.

Please post code into a code box opened on the forum with either the </> or  "running man" icon that appears above the message window. That will preserve your formatting of code and the message widows will reformat text usually removing white space and may do so in such a manner that pasted code will not run.

Best might also be to provide example data or referencing a common SAS supplied data set like SASHELP.CLASS, not very big only 19 records, provide what you expect the output to look like.

Proc tabulate can have multiple variables in a single dimension so ALL of your Score variables could be on a single VAR, then have them on the first dimension instead of only one. Then you will have one row for each variable in the output.

But without knowing exactly what you want/expect for output it is hard to make better suggestions.

[dustychair]

%macro part(s);


ods excel file="C:\Grade\scale_scores\ss_by_gr_&s..xlsx" style=minimal;
proc tabulate data=alt_&s. out=ss_by_gr_&s. ;
class grade;
var &s._scale_score;
table &s._scale_score*(format=comma16.) ,
grade *(N* format=comma16. Mean* f=6.2 Std*f=6.2);
run;
ods excel close;
PROC IMPORT OUT= ss_by_gr_&s. DATAFILE= "C:\Grade\scale_scores\ss_by_gr_&s..xlsx" 
DBMS=xlsx REPLACE;
GETNAMES=YES;
RUN;

data ss_by_gr_all;
set ss_by_gr_&s. ;
run;

%mend;
%part (listening);
%part (reading);
%part (speaking);
%part (writing);
%part (oral);
%part (literacy);
%part (comprehension);
%part (overall);

Thank you for your time @ballardw. It seems there is a lot that I need to learn. There are so many data sets, so I don't know which one I should post as an example. You can find the macro that I am using above.

Thanks

Thank

[Kurt_Bremser · Accepted solution]

This part of your macro is the problem:

data ss_by_gr_all;
set ss_by_gr_&s. ;
run;

The dataset will be overwritten with every macro call, so only the result of the last execution will make it.

Use PROC APPEND instead, and add code to clear the results dataset (handy if you run the whole process repeatedly):

%macro part(s);

/* code to create dataset ss_by_gr_&s. */

proc append
  data=ss_by_gr_&s.
  base=ss_by_gr_all
  force
;
run;

%mend;

/* check if the result dataset already exists and remove it, if it is so */
%if %sysfunc(exist(ss_by_gr_all))
%then %do;

proc delete data=ss_by_gr_all;
run;

%end;

%part (listening);
/* further calls to the macro */

[andreas_lds]

This looks really complicated.

Why not combining all datasets and using an additional class-variable in proc tabulate?

[ballardw]

And what is the desired result?

If the overall purpose is to get counts and means of a number of a number of variables into a single data set i would be extremely tempted to

1) combine the data sets and

2) use a different procedure like Proc Summary.

Here is an example where I create 3 data sets combine the data and do the summary.

Note: If you started this process by splitting the data into multiple data steps then that was a sub optimal choice (and usually is).

data a;
   input grade a_score;
datalines;
1  23
1  35
1  44
1  88
2  33
2  45
2  54
2  68
;
data b;
   input grade b_score;
datalines;
1  29
1  39
1  49
2  39
2  49
2  59
2  69
2  99
;
data c;
   input grade c_score;
datalines;
1  43
1  45
1  44
1  48
1  49
2  36
2  47
2  57
2  67
;

data combined;
   set a b c; /* our use proc append*/
run;

proc summary data=combined ;
   class grade;
   var a_score b_score c_score;
   output out=want 
      n= mean= /autoname;
run;
/* for a nicer report*/
proc tabulate data=combined;
   class grade;
   var a_score b_score c_score;
   table  a_score b_score c_score,
          (grade all='all grades')*(n mean)
   ;
run;

The proc summary output will have a varaible _type_ that shows the combinations of the Class variables. In this case 0, which is a summary across all grades, and by each grade. The Autoname creates variables with the statistic name appended to the variable so you can tell which value it represents. If the separate summaries are not needed then the option NWAY would be added to the Proc statement to only show the highest combination.

This option of creating multiple summaries is extremely powerful because you can create them with one pass through the data, with all the output in one set (no need to recombine data) and you can select specific records as needed with a Where statement and the proper value(s) of the _type_ variable.

Note: if you name variables with a common value at the beginning like Score_a Score_B Score_c instead of A_score B_score and C_score you can reference many variables in list, such as for a VAR statement in Tabulate or Summary as a list like Score_:  . The colon at the end tells SAS you want to use All of the variables whose names start with Score_.

If have your data with a variable to indicates what the "score" represents.Such as

Grade Measure Score then a single data set is usually more flexible.

Now you have three small example data sets you can show, using them, exactly where this process is going, or what you expect the final output to look like.