topic Re: Create data table to create layout for ODS PDF in SAS Procedures

Create data table to create layout for ODS PDF

jerry898969 — Fri, 09 May 2014 18:41:27 GMT

Hello,

I have a data set I want to create a pdf out of. The issue is that I want my data laid out in a certain way and I can't seem to do it with proc report so I wanted to change my data set so it conforms to what I want.

MyData

Year Question AnsCode AnsText QType

2010 "Is this the test 1?" 1 "Yes" "Intro"

2010 "Is this the test 1?" 2 "No" "Intro"

2010 "Is this the test 1?" 3 "N/A" "Intro"

2010 "Is this the test 2?" 1 "Yes" "Sec1"

2010 "Is this the test 2?" 2 "No" "Sec1"

2010 "Is this the test 2?" 3 "N/A" "Sec1"

2014 "Is this the test 3?" 1 "Yes" "Intro"

2014 "Is this the test 3?" 2 "No" "Intro"

2014 "Is this the test 3?" 3 "N/A" "Intro"

This is how I want to display it

2010

Intro

Is this the test 1?

CODES:

1 Yes

2 No

3 N/A

Sec1

Is this the test 2?

CODES:

1 Yes

2 No

3 N/A

2014

Intro

Is this the test 3?

CODES:

1 Yes

2 No

3 N/A

Can I create a dataset like this and then use it in my proc report or is there a way to do it with proc report?

Thank you

Re: Create data table to create layout for ODS PDF

Reeza — Fri, 09 May 2014 21:45:12 GMT

A standard data step with PUT statements is what I'd probably recommend.

Re: Create data table to create layout for ODS PDF

stat_sas — Fri, 09 May 2014 22:18:46 GMT

May be this can generate your desired output.

data have;

input Year Question $ 6-27 AnsCode AnsText $ 30-35 QType $;

datalines;

2010 "Is this the test 1?" 1 "Yes" "Intro"

2010 "Is this the test 1?" 2 "No" "Intro"

2010 "Is this the test 1?" 3 "N/A" "Intro"

2010 "Is this the test 2?" 1 "Yes" "Sec1"

2010 "Is this the test 2?" 2 "No" "Sec1"

2010 "Is this the test 2?" 3 "N/A" "Sec1"

2014 "Is this the test 3?" 1 "Yes" "Intro"

2014 "Is this the test 3?" 2 "No" "Intro"

2014 "Is this the test 3?" 3 "N/A" "Intro"

;

run;

proc tabulate data=have;

class year qtype question anscode;

table year=' '*qtype=' '*question=' '*anscode=' ',n=' ';

run;

Re: Create data table to create layout for ODS PDF

Ksharp — Sat, 10 May 2014 03:42:33 GMT

data have;
input Year Question $ 6-27 AnsCode AnsText $ 30-35 QType $;
question=dequote(question);anstext=dequote(anstext);qtype=dequote(qtype);
datalines;
2010 "Is this the test 1?" 1 "Yes" "Intro"
2010 "Is this the test 1?" 2 "No"   "Intro"
2010 "Is this the test 1?" 3 "N/A"  "Intro"
2010 "Is this the test 2?" 1 "Yes" "Sec1"
2010 "Is this the test 2?" 2 "No"   "Sec1"
2010 "Is this the test 2?" 3 "N/A"  "Sec1"
2014 "Is this the test 3?" 1 "Yes"  "Intro"
2014 "Is this the test 3?" 2 "No"    "Intro"
2014 "Is this the test 3?" 3 "N/A"   "Intro"
;
run;


data report;
 set have;
 by  Year QType     Question;
 length y t q x z$ 40;
 if first.Question then do;
  y=year;output; y=' ';
  t=qtype;output;t=' ';
  q=question;output;
  q='CODES:';output; q=' ';
  end;
x=anscode;z=anstext;output;
keep y t q x z;
run;
ods listing close;
ods pdf file='x.pdf';
proc report data=report nowd noheader;
run;
ods pdf close;
ods listing;

Xia Keshan

Re: Create data table to create layout for ODS PDF

jerry898969 — Mon, 12 May 2014 13:05:06 GMT

Thank you Ksharp,

This leads me into a better direction in getting this pdf file created and displayed like the old way.

Thank you so much

Re: Create data table to create layout for ODS PDF

jerry898969 — Mon, 12 May 2014 13:06:50 GMT

Thank you stat@sas,

This also is another good way to look at my issue. I will look at both versions and try and create the desired pdf.

Thank you

Re: Create data table to create layout for ODS PDF

RW9 — Mon, 12 May 2014 13:56:09 GMT

Hi,

You can also use escape codes to achieve the same effect:

data have;
attrib Year Question AnsCode AnsText QType format=$200.;
infile datalines delimiter=",";
input year $ question $ anscode $ anstext $ qtype $;
datalines;
2010,"Is this the test 1?",1,"Yes","Intro"
2010,"Is this the test 1?",2,"No","Intro"
2010,"Is this the test 1?",3,"N/A","Intro"
2010,"Is this the test 2?",1,"Yes","Sec1"
2010,"Is this the test 2?",2,"No","Sec1"
2010,"Is this the test 2?",3,"N/A","Sec1"
2014,"Is this the test 3?",1,"Yes","Intro"
2014,"Is this the test 3?",2,"No","Intro"
2014,"Is this the test 3?",3,"N/A","Intro"
;
quit;

data inter (keep=outline);
set have;
attrib outline format=$2000.;
by year;
if first.year then outline=strip(year)||"^n "||strip(qtype)||"^n "||strip(question)||"^nCODES:^n "||strip(anscode)||" "||strip(anstext);
else outline=" "||strip(anscode)||" "||strip(anstext);
run;

ods escapechar="^";
ods pdf file="s:\temp\rob\test.pdf";
proc report data=inter nowindows style(column)=[just=l borderrightcolor=white borderleftcolor=white
bordertopcolor=white borderbottomcolor=white];
define outline / style(column)=[width=8cm asis=on];
run;
ods pdf close;

Re: Create data table to create layout for ODS PDF

jerry898969 — Mon, 12 May 2014 14:48:05 GMT

Hi Ksharp,

How can I add links to question and qtype to have them point to different locations within the PDF file I'm creating?

Thank you

Re: Create data table to create layout for ODS PDF

RW9 — Mon, 12 May 2014 14:55:41 GMT

You would need to use ods anchor, please see thread:

https://communities.sas.com/thread/42000

Re: Create data table to create layout for ODS PDF

jerry898969 — Mon, 12 May 2014 15:10:28 GMT

Hi RW9,

I know I have to use an anchor but going off of KSharps example I could I implement the anchor on the question and the qtype?

How come carriage returns in my data don't work within ODS?

Thank you again for your help

Re: Create data table to create layout for ODS PDF

RW9 — Mon, 12 May 2014 15:20:12 GMT

Its not straightforward, you would either create a macro or do a call execute, to generate each output, so print row one with anchor xx, then print next one with anchor yy, not breaking between each one. Check out the post: 09-May-2014 04:49 from:

https://communities.sas.com/message/210632#210632

By carriage returns do you mean the embedded ^n. If so then you need the line ods escapechar="^"; If you mean another character then maybe switch to the n, some things that work in RTF for instance don't work in PDF.

Re: Create data table to create layout for ODS PDF

data_null__ — Mon, 12 May 2014 15:25:01 GMT

I've been learning and experimenting with PDF output lately too and thought it might be a good exercise to do this with LINE statements. I don't know about linking within the document I will be interested to see how/if that is done.

I was a bit disappointed that I had to resort to PROTECTSPECIALCHAR to get the last indentation level for CODES:. I hope comes along and schools us all.

data q;
   input Year Question &$quote64. AnsCode  (AnsText QType)(&$quote8.);
   cards; 
2010 "Is this the test 1?"  1 "Yes"  "Intro"
2010 "Is this the test 1?"  2 "No"   "Intro"
2010 "Is this the test 1?"  3 "N/A"  "Intro"
2010 "Is this the test 2?"  1 "Yes"  "Sec1"
2010 "Is this the test 2?"  2 "No"   "Sec1"
2010 "Is this the test 2?"  3 "N/A"  "Sec1"
2014 "Is this the test 3?"  1 "Yes"  "Intro"
2014 "Is this the test 3?"  2 "No"    "Intro"
2014 "Is this the test 3?"  3 "N/A"   "Intro"
;;;;
   run; 
proc print; 
   run; 
ods pdf file='~/multilevelstub.pdf'; 
proc report nowd list out=rept style(lines)=[TEXTALIGN=left];
   column year qtype question anscode anstext;
   define year / order order=internal noprint; 
   define qtype / order order=internal noprint; 
   define question / order order=internal noprint; 
   define anscode / order order=internal ' ' format=1. style=[marginleft=.75in];
   define anstext / display ' '; 
   compute before year / style=[marginleft=0in];
      line year 4.-l;
      endcomp; 
   compute before qtype / style=[marginleft=.125in];
      line qtype $8.; 
      endcomp; 
   compute before question /style=[marginleft=.25in];
      line question $64. '(*ESC*){newline}(*ESC*)S={protectspecialchars=on}   CODES:'; 
      endcomp; 
   run; 
ods pdf close; 
proc print; 
   run;

Re: Create data table to create layout for ODS PDF

jerry898969 — Mon, 12 May 2014 16:54:47 GMT

KSharp & data_null_;

thank you both so much for you replies.

It's been so frustrating trying to replicate this other pdf file using SAS. I thought it would be more straight forward then it is. Of course it's escapechar i've used it before but i'm so flustered.

Thanks again and I will reply with any new or helpful information.

Re: Create data table to create layout for ODS PDF

ballardw — Mon, 12 May 2014 20:01:11 GMT

Are you going to populate any additional columns with counts or other statistics related to the AnsCode or AnsText?

If so proc tabulate might work with something like:

Proc tabulate data=q; /* to use someone elses data set*/

class Year Question AnsCode AnsText QType; /* assuming none of these variables have missing values*/

table year=""*Qtype=""*Question=""*AnsCode="Codes"*AnsText;

run;

I suspect that if you have actual response data you could skip whatever this step is doing and create summary tables directrly from the data with counts, means or whatever summaries you need from the data. It looks to me that you may be building an empty table that you will then enter data into manually. It might not hurt to describe the next couple of things you are doing with your data.

Re: Create data table to create layout for ODS PDF

Cynthia_sas — Tue, 13 May 2014 02:32:46 GMT

Hi:

Sorry, I'm late to the party. I was out of town and without my computer.

The OP already has a lot of possible solutions. Data _NULL_, I don't know why you needed PROTECTSPECIALCHARS, especially since ON is the default. But I do have a faint memory of how PDF is not always happy about changing margins inside a single cell, which is what you're creating with your single LINE statement. As you can see, in your example, the string CODES is inside the same line cell as the question. And you already gave that cell a left margin. But I don't think I would have tried to use ODS ESCAPECHAR to try to change the margin, for the reason stated above. I don't believe that you can successfully use a style override in the middle of a single line (even with NEWLINE, it's still 1 line), when the style of the "container" cell has already been specified. That would be a question for the PDF experts in Tech Support.

However, to me, it's moot. I would have approached it a bit differently. I either would have made a single Since the report is so straightforward, I would have just used another variable to hold the "code string" -- which in my program I call the CODESTR variable whose value is set to CODES: and then I can just do a COMPUTE before CODESTR.

You made fixed lengths for all your questions and text, but I prefer to use $varying. in my LINE statements. I think it makes it easier and then since I can use the LENGTH function to find out the exact length of the string I'm about to write, I don't have to worry about one question being or string being really short and another really long.

Then, I used a STYLE(REPORT) override to get rid of interior table lines and the frame box of the table -- this looks more like an outline form or documentation to me.

As an alternative, I would probably have just transposed the data and made some fake ordering variables so that there were only 2 basic character columns on the REPORT -- COL1 and COL2, as shown in the "alt" report. With this approach, the order is imposed either by the order of the data or thru a program and PROC REPORT margins are set based on one of the ordering variables. In the second report, I varied the question text a bit, so that I could test a long question wrapping and used the order variables also to control the order of the codes, since they might not always be in a standard order.

cynthia

data q;
   infile datalines;
   input Year Question &$quote64. AnsCode (AnsText QType)(&$quote8.);
   codestr = 'CODES:';
return;
datalines4;
2010 "Is this the test 1?" 1 "Yes" "Intro"
2010 "Is this the test 1?" 2 "No"   "Intro"
2010 "Is this the test 1?" 3 "N/A" "Intro"
2010 "Is this the test 2?" 1 "Yes" "Sec1"
2010 "Is this the test 2?" 2 "No"   "Sec1"
2010 "Is this the test 2?" 3 "N/A" "Sec1"
2014 "Is this the test 3?" 1 "Yes" "Intro"
2014 "Is this the test 3?" 2 "No"    "Intro"
2014 "Is this the test 3?" 3 "N/A"   "Intro"
;;;;
run;
ods listing;
title; footnote;

proc print data=q;
var year qtype question codestr anscode anstext;
run;

ods listing close;
ods pdf file='c:\temp\other_report.pdf' style=printer;
proc report data=q nowd
     style(lines)=[TEXTALIGN=left]
     style(header)={background=white}
     style(report)={rules=none frame=void cellspacing=0 cellpadding=2px};
   column year qtype question codestr anscode anstext;
   define year / order order=internal noprint;
   define qtype / order order=internal noprint;
   define question / order order=internal noprint;
   define codestr / order noprint;
   define anscode / order order=internal ' ' format=1. style(column)={just=r};
   define anstext / display ' ' style(column)={just=r};
   compute before year ;
      line year 4.;
   endcomp;
   compute before qtype / style=[marginleft=.125in];
      tlg = length(qtype);
      line qtype $varying. tlg;
   endcomp;
   compute before question / style={marginleft=.25in};
      qlg = length(question);
      line question $varying. qlg;
   endcomp;
   compute before codestr / style={marginleft=.375in};
    clg = length(codestr);
       line codestr $varying. clg ;
   endcomp;
run;
ods pdf close;
title; footnote;

data alt_q;
   length col1 $80 col2 $50;
   infile datalines dlm=',' dsd;
   input yrord ord year col1 $ col2 $;
   codestr = 'CODES:';
return;
datalines4;
1,10,2010,"2010"," "
1,11,2010,"Intro"," "
1,12,2010,"Is this test 1?"," "
1,13,2010,"CODES:"," "
1,14,2010,"1","Yes"
1,15,2010,"2","No"
1,16,2010,"3","N/A"
2,11,2010,"Sec1"," "
2,12,2010,"Is this longer for 2?"," "
2,13,2010,"CODES:"," "
2,14,2010,"0","Yes"
2,15,2010,"1","No"
2,16,2010,"2","N/A"
2,17,2010,"9","Other"
3,10,2014,"2014"," "
3,11,2014,"Intro"," "
3,12,2014,"Is this the very very very very very long long long question for test 3?"," "
3,13,2014,"CODES:"," "
3,14,2014,"A","Alpha"
3,15,2014,"B","Beta"
3,16,2014,"C","Gamma"
3,17,2014,"D","Delta"
;;;;
run;
ods listing close;
title; footnote;

ods pdf file='c:\temp\alt_report.pdf' style=printer;
proc report data=alt_q nowd noheader
     style(header)={background=white}
     style(report)={rules=none frame=void cellspacing=0 cellpadding=2px};
   column year yrord ord col1 col2;
   define year / order noprint;
   define yrord / order noprint;
   define ord / order noprint;
   define col1 / display
          style(column)={width=2in};
   define col2 / display
          style(column)={width=1in};
   compute col1;
      if ord = 10 then do;
      call define(_col_,'style','style={just=l}');
   end;
   if ord = 11 then do;
      call define(_col_,'style','style={marginleft=0.15in }');
   end;
   else if ord = 12 then do;
      call define(_col_,'style','style={marginleft=0.3in }');
   end;
   else if ord = 13 then do;
      call define(_col_,'style','style={marginleft=0.45in }');
   end;
   else if ord gt 13 then do;
      call define(_col_,'style','style={just=r}');
   end;
   endcomp;
run;
ods pdf close;
title; footnote;

Re: Create data table to create layout for ODS PDF

jerry898969 — Tue, 13 May 2014 12:26:29 GMT

Hi Cynthia,

Thank you so much for your reply.

On the alt_report is there a way to have the long question merge into the second column so the text is on top of the codes?

Thanks again for all the help.

Re: Create data table to create layout for ODS PDF

Ksharp — Tue, 13 May 2014 13:05:50 GMT

OK. Here is . You can link the qtype to the right place within the same pdf .and the same principle for Questions. I think you can do it on your own.

data have;
input Year Question $ 6-27 AnsCode AnsText $ 30-35 QType $;
question=dequote(question);anstext=dequote(anstext);qtype=dequote(qtype);
datalines;
2010 "Is this the test 1?" 1 "Yes" "Intro"
2010 "Is this the test 1?" 2 "No"   "Intro"
2010 "Is this the test 1?" 3 "N/A"  "Intro"
2010 "Is this the test 2?" 1 "Yes" "Sec1"
2010 "Is this the test 2?" 2 "No"   "Sec1"
2010 "Is this the test 2?" 3 "N/A"  "Sec1"
2014 "Is this the test 3?" 1 "Yes"  "Intro"
2014 "Is this the test 3?" 2 "No"    "Intro"
2014 "Is this the test 3?" 3 "N/A"   "Intro"
;
run;


data report;
 set have;
 by  Year QType     Question;
 length y t q x z$ 40;
 if first.Question then do;
  y=year;output; y=' ';
  t=qtype;output;t=' ';
  q=question;output;
  q='CODES:';output; q=' ';
  end;
x=anscode;z=anstext;output;
keep y t q x z;
run;

data Intro;
 x='asdfsadfasfda';
run;
data Sec1;
 x='asdfasdfad';
run;





ods listing close;
ods pdf file='x.pdf' style=sasweb;
title1 'Summary';
proc report data=report nowd noheader;
compute t;
rtag = "#"||trim(t);
if not missing(t) then call define(_col_,'url',rtag);
endcomp;
run;


ods pdf anchor="Intro" startpage=now;
ods proclabel="Intro";
title1 'Intro Summary';
proc report data=Intro nowd;run;


ods pdf anchor="Sec1" startpage=now;
ods proclabel="Sec1";
title1 'Sec1 Summary';
proc report data=Intro nowd;run;




ods pdf close;
ods listing;

Good Luck

Xia Keshan

Message was edited by: xia keshan

Re: Create data table to create layout for ODS PDF

jerry898969 — Tue, 13 May 2014 13:40:14 GMT

Thank you Ksharp for all your help. I'm slowly piecing everything together with everyone help.

I will post all my code when I have it generating correctly.

Thank you

Re: Create data table to create layout for ODS PDF

Cynthia_sas — Tue, 13 May 2014 22:30:39 GMT

No, the question is in the COL1 variable, so it will be confined to the COL1 cell and cannot span on the top of the COL2 cell. If that's what you want, then the first example, that created "other_report.html" is the better example to follow because what is written with the LINE statement does span the whole table. The program I posted was a modification of DATA _NULL_'s example.

cynthia

Re: Create data table to create layout for ODS PDF

jerry898969 — Wed, 14 May 2014 14:59:20 GMT

Hi Cynthia,

thank you for your reply. Here is my code so far using the first approach:

ods listing ;

title ;

footnote ;

options orientation=portrait topmargin=.5in bottommargin=.5in rightmargin=.5in leftmargin=.5in ;

options nodate nonumber nocenter ;

ods listing close ;

ods pdf file='H:\shared\jwarra\SAS_ODS_PDF\other_report.pdf' style=Temp ;

ods pdf uniform ;

proc report data=x nowd

style(lines)=[TEXTALIGN=left]

style(header)={background=white}

style(report)={rules=none frame=void cellspacing=0 cellpadding=2px} ;

column year cname vname ;

define year / order order=internal noprint ;

define cname / order order=internal noprint ;

define vname / order order=internal noprint ;

compute before year / style={font_weight=bold font_face=Courier} ;

line year 4. ;

endcomp ;

compute before cname / style={font_weight=bold font_face=Courier marginleft=.50in} ;

qlg = length(cname) ;

line cname $varying. qlg ;

endcomp ;

compute before vname / style={font_face=Courier marginleft=.90in} ;

qlg = length(vname) ;

urlstring="#"||strip(vname) ;

call define('vname',"URL",urlstring) ;

line vname $varying. qlg ;

endcomp ;

run;

ods pdf close ;

title; footnote ;

I have the layout working like I needed it.

2010

Intro

V1234 - Variable 1

V5678 - Variable 2

Sec1

V0009 - Variable 3

2014

Into

V4562 - Variable 4

My issue now is that when I try and do a compute with vname and add a url it doesn't generate a url on the pdf file.

Should I remove the line option and make vname display instead of noprint? Is there a way I can make the text the link not

the cell?

Thank you everyone for your help.