topic Re: Manipulate data within ODS PDF in ODS and Base Reporting

Manipulate data within ODS PDF

jerry898969 — Thu, 08 May 2014 12:49:19 GMT

Hello,

I'm very new to ODS and i'm trying to create an automated process that will take multiple data sets and create a pdf file displaying the meta of the data.

Is there a way through ODS to know how many observations a data set has? I want to show the users that the data they created has n rows, without having to create a seperate query or data set to have the count value.

Thank you

Re: Manipulate data within ODS PDF

Cynthia_sas — Thu, 08 May 2014 13:02:07 GMT

Hi:

I'm confused about what you want. You ask whether there is "a way through ODS to know how many observations a data set has"? And the answer is yes, but then you say "without having to create a separate query or data set to have the count value"? You'd have to at least run SOMETHING, because ODS OUTPUT can capture the output from PROC CONTENTS about the number of observations, but ODS OUTPUT would create a dataset, and that's what you say you don't want to do. But using ODS OUTPUT for this is like killing flies with a sledgehammer. It is far easier to write an SQL query against one of the DICTIONARY tables and make a macro variable -- you don't need ODS with PROC SQL to make a macro variable. But then you HAVE a macro variable to use anywhere -- in an ODS step, in a DATA step program, in a TITLE statement. ODS, by itself, has no "magic" way of getting the observation count. ODS OUTPUT creates a data set from a procedure; and ODS destinations create output files. Nothing about ODS goes straight to the descriptor portion of the data set.

The code below doesn't seem too troublesome to me. What is the issue with something like this?

Cynthia

proc sql noprint;

select nobs into :numobs

from dictionary.tables

where libname = "SASHELP" and

memname = "CARS";

quit;

ods _all_ close;

%put how many cars = &numobs;

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

proc print data=sashelp.cars(obs=10);

title "Showing 10 obs of &numobs";

var make model type msrp;

run;

ods html close;

Re: Manipulate data within ODS PDF

jerry898969 — Thu, 08 May 2014 14:23:09 GMT

Hi Cynthia,

I was hoping that there was a way to get the observation count out of my data table without having to create a macro variable. I need to do this for multiple data tables and didn't want to have to create a macro variable for each. I wanted to limit the number of statements. I also don't want to show all the data with the count. I'm creating the first page of my pdf that shows the number of data rows, a title and a data id. These are all coming from different queries. How can I put all three on one page and style them?

Thank you for your code that helped with a separate issue.

Re: Manipulate data within ODS PDF

RW9 — Thu, 08 May 2014 14:36:41 GMT

You could query the sashelp.vtable table which has the obs in:

data counts (keep=libname memname nobs);
set sashelp.vtable;
where libname="SASHELP" and memname in ('CARS','CLASS','BMT');
run;

proc print data=counts;
run;

Re: Manipulate data within ODS PDF

jerry898969 — Thu, 08 May 2014 15:16:39 GMT

Thank you RW9 for your help.

I have to add the count to a text field. How can I do that with the nobs variable?

The difficult part is taking variables from multiple data sets and trying to place them in certain positions within the first and second pages of my ods pdf file

i'm trying to create.

Thank you again for your help.

Re: Manipulate data within ODS PDF

RW9 — Thu, 08 May 2014 15:29:52 GMT

For using the nobs var in a text field that's pretty straightforward.

text_field=strip(put(nobs,best.))||" "||strip(text_field);

With regards to your second point, you might consider using some SQL, for instance create an empty table, and then insert rows as you require. You have DICTIONARY.COLUMNS and TABLES you can select from (or the VTABLE, VCOLUMN in SASHELP), and join insert the data as needed.

If you could post some test input and required output I would be able to suggest further. I am leaving for the day but can get back to this tomorrow.

Re: Manipulate data within ODS PDF

jerry898969 — Thu, 08 May 2014 15:43:11 GMT

Thank you RW9 for your help.

The first page of my pdf will have an "id: ######" then about 5 rows from there I will have the Title="Data Descripition" and then under that I will have "Number of rows: ##" The "#" will be values coming out of my first data set.

ID VAR vLabel

0001 vTest1 TEST LABEL 1

0001 vTest2 TEST LABEL 2

based on above data the first page will look like this but everything should be centered and rows between them:

"id: 0001"

"Data Descripition"

"Number of rows:02"

Then I need to create a table of contents using my variable names and have them link to the data displayed coming out of my second data set.

vTest1(link) TEST LABEL 1 VALUES(link)

vTest2(link) TEST LABEL 2 VALUES(link)

my second data set will have

VAR valueid Value Text

vTest1 1 Yes THIS IS USED FOR vTest1

vTest1 2 No THIS IS USED FOR vTest1

vTest2 1 Yes THIS IS USED FOR vTest2

vTest2 2 Maybe THIS IS USED FOR vTest2

vTest3 3 No THIS IS USED FOR vTest2

The values link will link to the variable values

vTest1 TEST LABEL 1

THIS IS USED FOR vTest1

1 Yes

2 No

vTest2 TEST LABEL 2

THIS IS USED FOR vTest2

1 Yes

2 Maybe

3 No

Thank you again for all your help. Have a great day.

Re: Manipulate data within ODS PDF

RW9 — Fri, 09 May 2014 08:49:02 GMT

Hi,

Whoa, you are asking a lot in that mail. Am going to split this over two posts. First I attach a small section of code which shows how to get the information out of the sashelp tables, and assign pdf anchors. Note that your anchor needs to be all character (no numbers). Here is the code which whilst not exactly what you want can be fiddled around with:

/* Get information required */
proc sql;
create table HEADER as
select distinct
          "id: "||strip(MEMNAME)||"^nData Description: "||strip(MEMLABEL)||"^nNumber of Rows: "||strip(put(NOBS,best.)) as INFO
          /* The above has an escape character for newline to get the information on next row in output */
from    SASHELP.VTABLE
where   LIBNAME="SASHELP"
    and   MEMNAME in ("CARS","CLASS");
quit;
proc sql;
create table VARS as
select MEMNAME,
          NAME,
          "^S={just=left vjust=middle cellheight=20pt font_size=10pt cellwidth=15cm linkcolor=white url='#"||strip(MEMNAME)||strip(NAME)||"'} "||strip(MEMNAME)||", "||Strip(NAME) as ANCHOR
          /* Note the url= which sets the link from main page, and it needs to be all character, preceded by hash, and match something later on */
from    SASHELP.VCOLUMN
where   LIBNAME="SASHELP"
    and   MEMNAME in ("CARS","CLASS");
quit;

/* Creating PDF */
ods pdf file="s:\temp\rob\Tmp.pdf" style=statistical;
ods escapechar="^"; /* The escape char for new lines etc. */
proc report data=header nowd contents="";
columns info;
define info / " ";
run;
proc report data=vars nowd contents="";
columns memname name anchor;
define memname / noprint;
define name / noprint;
define anchor / " ";
run;

/* This loops over each varriable in the vars dataset and generates a proc report for each one, with an anchor set which matches the main page */
data _null_;
set vars;
call execute('ods pdf anchor="'||strip(memname)||strip(name)||'";
                proc report data=vars nowd contents="";
                  columns memname name;
                  define memname / "Mem name";
                  define name / "Name";
                  where memname="'||strip(memname)||'" and name="'||strip(name)||'";
                run;');
run;
ods pdf close;

Re: Manipulate data within ODS PDF

RW9 — Fri, 09 May 2014 08:55:00 GMT

Now, following on from that example, the next thing I wanted to ask is what you are trying to do. From your description I assume you are trying to create if not exactly, similar to, a define.xml file. This isn't a simple thing. I would suggest that you start by investigating the CDSIC documentation:

http://www.cdisc.org/define-xml

There are a number of tools out there to help with the creation of this, for example SAS has its own Clinical Data Toolkit, which has options to create define. It is more about setting your metadata up first though. For instance many companies have a specifications document which details each domain, variable and codelist, and that spec is used to create both the output datasets and the define document. You could create your metadata by hand, create list of datasets, from that create list of variables, from that create list of uniques within data, each step looping over the previous. However I would suggest then that you would be generating your specs from the data, rather than the specs generating how the data looks, and trust me the latter of those two is the way to go = specs first, then program from that!

Anyways, hope the code helps, and definitely look at define.pdf/xml.

Re: Manipulate data within ODS PDF

jerry898969 — Fri, 09 May 2014 12:38:10 GMT

RW9,

Thank you so much for your help. I will look into all options you mentioned. The old process we had was like a "MS access" report where you could bind a query to a report and just layout the fields on the report and then the report would be executed and generate with the data from the query. Is the spec something like that?

No link within the PDF can have a number as part of the display? That is going to be a big problem since all the data I need to link has numbers.

Thank you again for all your help it is appreciated

Re: Manipulate data within ODS PDF

RW9 — Fri, 09 May 2014 12:58:42 GMT

Yes, MS forms are quite visual. Base SAS doesn't have much in the way of visual report making, its primarily code based. The term 'spec' stands for any kind of specification. For me we tend to have CDISC based domain/variable specs, you can see from the metadata provided by them. Its a description of what datasets are present,what variables each dataset contains, what code lists are used etc. This is then translatd into XML, and a stylesheet attached which gives a nice web page based linked file.

Yes, that is right, see my thread on: https://communities.sas.com/thread/42000

However, it shouldn't be a problem. Just pre-process your data creating a new variable then translate the numbers into chars as it is only for a behind the scenes link:

my_anchor=tranwrd(varname,"1","A");

Then If your varname is HELLO1234, after processing the varname would be the same, but the anchor value would be HELLOABCD.

Re: Manipulate data within ODS PDF

jerry898969 — Fri, 09 May 2014 15:19:52 GMT

Hi RW9,

Here is the code that I have generated and I have it some what working. My issue is the purple box that is around my variable that is my link. How can I remove this purple border and make the text the link not the cell?

options nodate nonumber ;

ods listing close;

ods pdf file="c:\temp\Tmp.pdf" style=statistical;

ods escapechar="^"; /* The escape char for new lines etc. */

proc report data=xyz nowd contents="" ;

columns year3 name2 varname label ;

define year3 / " " ;

define name2 / " " ;

define varname / " " ;

define label / " " ;

compute varname ;

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

call define('varname','url',urlstring) ;

endcomp ;

run ;

data _null_;

set x ;

call execute('ods pdf anchor="'||strip(varname)||'";

proc report data=y nowd contents="";

columns varname label ;

define varname / "VARNAME";

define label / "LABEL";

where varname="'||strip(varname)||'";

run;');

run;

ods pdf close;

Thank you

Re: Manipulate data within ODS PDF

Vish33 — Sat, 10 May 2014 07:42:48 GMT

Hi,

Here is the macro which i used to find the number of records in a dataset .

/******check the data exists in the report or not, then send mail notification******/

%macro check(dsn);

%global nobs;

/*%let nobs=.;*/

* Open the data set of interest;

%let dsnid = %sysfunc(open(&dsn));

* If the open was successful get the;

* number of observations and CLOSE &dsn;

%if &dsnid %then %do;

%let nobs=%sysfunc(attrn(&dsnid,nlobs));

%let rc =%sysfunc(close(&dsnid));

%end;

%else %do;

%put Unable to open &dsn - %sysfunc(sysmsg());

%end;

* Return the number of observations;

%put &nobs;

%mend;

%check(work.mape);

%put &nobs;