BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yash82
Calcite | Level 5

Guys.

My data is something like this

data temp;

input Period   group $  Class_code & $40. year  Number  Gross_at_start  Gross_between  Gross_at_end;

datalines;

201112  Medical   Opened at start - open   2010  100  100.00  100.00  100.00

201112  Medical   Open at start - closed with payment   2009  122  133.00  144.00  155.00

201112  Medical   Open at start - closed without payment   2010  140  160.00  180.00  200.00

;

run;

I ran following piece of code

proc tabulate data=temp ;

class  Period  Class_code;

var  Gross_at_start  Gross_between  Gross_at_end ;

table Period ,Class_code*(Gross_at_start  Gross_between  Gross_at_end );

run;

It is producing correct output now my requirement is if  class_code is opened at start open (the first observation) then in my proc tabulate \i want only Gross_at_start and Gross_between  columns and for all remaining observation I want all column, How can this be achieved ??? Anyhelp is really appreciated....

I have already posted the same topic in Procedures section but I dont know how to move or close it . Request moderators to remove that from procedures section...

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  You say that your "TABULATE code is producing correct output". Which is good. Then you say that "now my requirement is if  class_code is opened at start open (the first observation) then in my proc tabulate \i want only Gross_at_start and Gross_between  columns and for all remaining observation I want all column"
                   
    Here is some basic information about SAS report procedures (REPORT, PRINT, TABULATE): When SAS builds a report table, it creates output where every report row has the same number of columns, so within one table, it is nearly impossible to show 5 columns on some rows, but only show 3 columns on other rows. (or, as you describe it -- conditionally display only some columns for some rows)  For the report procedures, PROC REPORT, in particular, you might need to show 5 columns on every row, but on selected rows, you would "blank out" the columns that you did not want to see (or something similar).

           

   PROC TABULATE works slightly differently from PROC REPORT -- PROC TABULATE produces summary tables with the possibility of many nestings or crossings in the row and column dimensions of the output table. PROC TABULATE doesn't really create a table with detail data -- that's more of a job for PROC PIRNT and/or PROC REPORT.

  For TABULATE, you could change the variables that you have nested under each of your column dimension variables, but I think that might not still give you the desired result that you are aiming for ("only Gross_at_start and Gross_between  columns and for all remaining observation I want all column"). And TABULATE has less ability to "blank out" column values in various report rows because it does not have COMPUTE block capability, like PROC REPORT.
           
  Also of importance is knowing what ODS destination you are ultimately going to use for your output files. You might find it easier, for example, to create ODS RTF results and then edit the table once the RTF file was opened with Microsoft Word -- or create ODS HTML results or TAGSETS.EXCELXP results and edit the table in Excel, since both Word and Excel allow merging of cells or deletion of cells in a point and click fashion that is not available in coding techniques.

  Without a clear picture of what you want to do, it's hard to speculate or make constructive suggestions. I do rememeber a posting over in the Procedures forum, where there was a screen shot of a "template" -- the picture did not look like SAS TABULATE output -- it looked more like the design of an Excel worksheet with Excel column references -- TABULATE doesn't work like that -- there are no A1-A10 column references in TABULATE when you are creating the tabular output. If you send the output from SAS to Excel, then your output might show up in A1-A10, but SAS and the report procedures do not create output in that way, so it is hard to correlate an Excel worksheet with formulas directly to TABULATE code.

  If you have an immediate need for help, you should open a track with SAS Tech Support. The support.sas.com page has an button on the left hand side of the page that says "Submit a Problem" -- that you can use to open a track.

cynthia

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

Hi:

  You say that your "TABULATE code is producing correct output". Which is good. Then you say that "now my requirement is if  class_code is opened at start open (the first observation) then in my proc tabulate \i want only Gross_at_start and Gross_between  columns and for all remaining observation I want all column"
                   
    Here is some basic information about SAS report procedures (REPORT, PRINT, TABULATE): When SAS builds a report table, it creates output where every report row has the same number of columns, so within one table, it is nearly impossible to show 5 columns on some rows, but only show 3 columns on other rows. (or, as you describe it -- conditionally display only some columns for some rows)  For the report procedures, PROC REPORT, in particular, you might need to show 5 columns on every row, but on selected rows, you would "blank out" the columns that you did not want to see (or something similar).

           

   PROC TABULATE works slightly differently from PROC REPORT -- PROC TABULATE produces summary tables with the possibility of many nestings or crossings in the row and column dimensions of the output table. PROC TABULATE doesn't really create a table with detail data -- that's more of a job for PROC PIRNT and/or PROC REPORT.

  For TABULATE, you could change the variables that you have nested under each of your column dimension variables, but I think that might not still give you the desired result that you are aiming for ("only Gross_at_start and Gross_between  columns and for all remaining observation I want all column"). And TABULATE has less ability to "blank out" column values in various report rows because it does not have COMPUTE block capability, like PROC REPORT.
           
  Also of importance is knowing what ODS destination you are ultimately going to use for your output files. You might find it easier, for example, to create ODS RTF results and then edit the table once the RTF file was opened with Microsoft Word -- or create ODS HTML results or TAGSETS.EXCELXP results and edit the table in Excel, since both Word and Excel allow merging of cells or deletion of cells in a point and click fashion that is not available in coding techniques.

  Without a clear picture of what you want to do, it's hard to speculate or make constructive suggestions. I do rememeber a posting over in the Procedures forum, where there was a screen shot of a "template" -- the picture did not look like SAS TABULATE output -- it looked more like the design of an Excel worksheet with Excel column references -- TABULATE doesn't work like that -- there are no A1-A10 column references in TABULATE when you are creating the tabular output. If you send the output from SAS to Excel, then your output might show up in A1-A10, but SAS and the report procedures do not create output in that way, so it is hard to correlate an Excel worksheet with formulas directly to TABULATE code.

  If you have an immediate need for help, you should open a track with SAS Tech Support. The support.sas.com page has an button on the left hand side of the page that says "Submit a Problem" -- that you can use to open a track.

cynthia

yash82
Calcite | Level 5

Thanks guys... I got the solution..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2 replies
  • 688 views
  • 0 likes
  • 2 in conversation