You would need to use ods anchor, please see thread: https://communities.sas.com/thread/42000 ... View more

Double post, please see other response. ... View more

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; ... View more

Hi, If you manipulate your data slightly then its simply a case of counting: data have;   attrib respid format=best. status format=$20. S7_1 S7_2 S7_3 S7_4 format=best.;   infile datalines delimiter=" " missover;   input respid status $ s7_1 s7_2 s7_3 s7_4; datalines; 8 complete 10    21 complete 10 9   54 complete 4 2   67 complete 10 9   75 complete 10 9   93 complete 10 8   111 complete 10 9   134 complete 10 9 6  169 complete 10 9 6 10 ; run; proc transpose data=have out=inter;   by respid;   var s7_1 s7_2 s7_3 s7_4; run; proc sql;   create table WANT as   select  distinct           COL1 as AGE,           count(RESPID) as FREQ   from    (select * from WORK.INTER where COL1 ne .)   group by COL1; quit; ... View more

Well, from my side if I wanted a spreadsheet, which updates itself based on some data then think about it from the other way round.  Create your spreadsheet as you would like to see it, formatting, layouts, graphs etc..  Setup a VBA macro which runs on startup and loads an Excel file located in a specific place.  That other Excel file will contain the data used  to create pivots/graphs etc.  Then all you would need to do is run the export from SAS as and when you like, overwriting the data Excel grid, and each time you open the main Excel file it would open the data as it is and update. ... View more

Not sure what you are trying to get to here, perhaps provide test data and what you want out.  There are various methods you could try, arrays for instance, or call executing to generate the code. ... View more

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. ... View more

The suggestion posted was merely for one method of doing such a task.  SAS also has arrays syntax:      array months {12} 3. (1,2,3,4,5,6,7,8,9,10,11,12); You could also look at this snippet: retain lstmonth; if month ne lstmonth then do;      do I=lstmonth to month-1;           month=I;           output;      end; end; output; So keep the last value of month, and if the current rows month is not the previous month +1 then output rows for the difference.  Note you need to check for month=12, and set other values to missing for these additional rows. As with most problems there is no one singular method to get what you want ... View more

perhaps you could post an example excel file where this problem occurs.  I cannot tell much from what you have posted.  ... View more

So here is the full code, tested this time.  The point is to create a dataset which will be the template for the other one. data have;   attrib id format=$1. month quantity format=best.;   infile datalines delimiter=",";   input id $ month quantity; datalines; a,1,100 a,2,200 a,4,150 b,1,60 ; run; /* Create template, need to base it on your have data */ proc sql;   create table IDS as   select  distinct ID   from    WORK.HAVE; quit; data template (drop=i);   set ids;   do I=1 to 12;     month=I;     output;   end; run; proc sql;   create table WANT as   select  COALESCE(A.ID,B.ID) as ID,              COALESCE(A.MONTH,B.MONTH) as MONTH,           A.QUANTITY   from    WORK.HAVE A   full join WORK.TEMPLATE B   on      A.ID=B.ID   and     A.MONTH=B.MONTH; quit; ... View more

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. ... View more

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; ... View more