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

Hi everyone,

I'm quite new to SAS and currently I'm trying to come up with a daily summary report that should look something like this:

Remarks
Overall Stock (%)90.34+1.00% from yesterday
Amount of tuna (Mg)4000.6-
Total Revenue ($ '000)30.1-

I have the individual values (Overall Stock, Amount of tuna, Total Revenue) in different queries (1 row each, 2 variables for overall stock and 1 variable for the other two) but how can I go about consolidating them into a table like this?

I understand that it is possible to join the queries vertically but this does not allow me to overwrite the formatting depending on where the value came from.

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  Again, without data, it is hard to comment in great detail. It appears that the data used by this program is not the same as the data with REMARKS that you posted originally.

  But there are some obvious issues. You do not have any ACROSS statements. So not sure why you are referencing _C2_ -- that is generally reserved for working with ACROSS items. If you want to change the value for DESCRIPTOR (which, conceptually might be thought of a _C2_), then the CALL DEFINE would be:

call define ('descriptor','format', '8.1'); or

call define('_c2_','format','8.1')

So, the issue with either reference is that even if you did properly quote the first argument, DESCRIPTOR is probably a character variable and can't take a numeric format. Also, it is incorrect to have both display and order as the usage for your ID variable. If you look at my example, my ORD variable was defined ONLY as an ORDER item on the report.

IF, on the other hand, you are trying to change the VALUEDIFF item, then _C2_ is absolutely the wrong way to reference that column for 2 reasons. 1) PROC REPORT works from left to right and VALUEDIFF is placed AFTER VALUE in the column statement. So you can't touch VALUEDIFF in the COMPUTE block for VALUE and 2) VALUEDIFF is NOT _C2_ -- you would need a separate compute block for VALUEDIFF.

  Modifying my previously posted sample data to include VALUEDIFF, I produced these results with the code shown in the screen shot. Notice that there is no reference to _C2_ needed. I changed the style as well as the format, so you could see the specific statements and where the changes were made.

cynthia

compute_block.png

cynthia

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its a common question this.  If your output has difffering data and formats, then the easiest way to get it is to create a shell dataset where all the variables are text.  Then you can put what you want in each cell, e.g. (and note I am just making up your data here, you will need to change it to fit what you have):

data template;

     length col1 col2 col3 $200;

     set overall_stock (in=a) amount_of_tuna (in=b) total_revenue (in=c);

     if a then do;

          col1="Overall Stock (%);

          col2=strip(put(stock,8.2));

          col3="+"||stript(put(pcent,8.2))||"% from yesterday";

          output;

     end;

     else if b then do;

          col1="Amount of tuna (Mg)";

          col2=strip(put(tuna,8.1));

          col3="-";

          output;

     end;

     else do;

               ...

     end;

run;

Cynthia_sas
SAS Super FREQ

Hi:

  PROC REPORT will allow you to use a different format on a different row, depending on the value of what is in the first column.

proc_report_call_define.png

So if you do restructure your data so that it is approximately in this format, your 2nd column could be all numeric and then you could use a CALL DEFINE statement to change the format for report purposes. The code that produced this screen shot is below. I made a "helper" variable called ORD to make it easier to test which format to use for which row and also because it appears you want Overall Stock to appear before Amount of tuna on the report and normally, PROC REPORT would put the A before the O. So the ORD variable is helping to set the non-alpha order that you want for the rows.

    

Cynthia

data testdata;
  length Type $20 val 8 remark $80;
  infile datalines dlm=',' dsd;
  input ord type $ val remark $;
   
  ** percent format will multiply by 100, so adjust number;
  if ord = 1 then val = val/100;
return;
datalines4;
1,"Overall Stock (%)",  90.34," +1.00% from yesterday"
2,"Amount of tuna (Mg)",  4000.6," - "
3,"Total Revenue ($ '000)",  30.1," - "
;;;;
run;

  

ods html file='c:\temp\diff_format.html';

    
proc report data=testdata;
  column ord type val remark;
  define ord / order noprint;
  define type / order order=data ' ';
  define val / display ' ';
  define remarks / display;
  compute val;
    if ord = 1 then
      call define(_col_,'format','percent9.2');
    else if ord = 2 then
      call define(_col_,'format','best8.1');
    else if ord = 3 then
      call define(_col_,'format','dollar9.1');
  endcomp;
run;
ods html close;

dory
Calcite | Level 5

Hi xia keshan, RW9 and Cynthia,

Thanks so much for your responses. I have a lot more rows than the table I included above, so a shell dataset will be quite time consuming to write and may not be easily maintained by other operators in future.

I have managed to convert the formats, but only for the first column. I tried to use

call define(_C2_,'format','percent9.2');

but the column doesn't change its format. Any advice? 😕

Cynthia_sas
SAS Super FREQ

Hi:

  You don't show enough code or data to make a constructive comment. It will really depend on the OTHER statements in your program and the usages that you have in your DEFINE statements. You might want to work with Tech Support on this.

cynthia

dory
Calcite | Level 5

Hi, this is what I have:

proc report data=qry_join

  noheader

  split='|';

  column ID Descriptor Value ValueDiff;

  define ID / noprint display order;

  define descriptor / display;

  define value / display center;

  define valuediff / display center;

  compute value;

  if ID=1 OR ID=2 then

  do;

  call define(_COL_,'FORMAT','8.2');

  call define(_C2_,'FORMAT','8.1');

  end;

  else do;

  call define(_COL_,'FORMAT','10.1');

  end;

  endcomp;

run;

And when I run it, I get these errors/notes:

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

ERROR: Invalid column specification in CALL DEFINE.

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

ERROR: Invalid column specification in CALL DEFINE.

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

Currently I have valuediff to show the +1.00% from yesterday instead of having the whole string.

Cynthia_sas
SAS Super FREQ

Hi:

  Again, without data, it is hard to comment in great detail. It appears that the data used by this program is not the same as the data with REMARKS that you posted originally.

  But there are some obvious issues. You do not have any ACROSS statements. So not sure why you are referencing _C2_ -- that is generally reserved for working with ACROSS items. If you want to change the value for DESCRIPTOR (which, conceptually might be thought of a _C2_), then the CALL DEFINE would be:

call define ('descriptor','format', '8.1'); or

call define('_c2_','format','8.1')

So, the issue with either reference is that even if you did properly quote the first argument, DESCRIPTOR is probably a character variable and can't take a numeric format. Also, it is incorrect to have both display and order as the usage for your ID variable. If you look at my example, my ORD variable was defined ONLY as an ORDER item on the report.

IF, on the other hand, you are trying to change the VALUEDIFF item, then _C2_ is absolutely the wrong way to reference that column for 2 reasons. 1) PROC REPORT works from left to right and VALUEDIFF is placed AFTER VALUE in the column statement. So you can't touch VALUEDIFF in the COMPUTE block for VALUE and 2) VALUEDIFF is NOT _C2_ -- you would need a separate compute block for VALUEDIFF.

  Modifying my previously posted sample data to include VALUEDIFF, I produced these results with the code shown in the screen shot. Notice that there is no reference to _C2_ needed. I changed the style as well as the format, so you could see the specific statements and where the changes were made.

cynthia

compute_block.png

cynthia

dory
Calcite | Level 5

Hi Cynthia,

Thanks for the detailed explanation. I didn't realise that _C2_ was restricted to ACROSS variables -- sorry, I'm quite new to SAS.

I managed to change the format for the second column doing the compute block like you suggested in your image.

Thank you!!

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
  • 8 replies
  • 6116 views
  • 0 likes
  • 4 in conversation