About RW9

RW9 · ‎07-14-2014

Hi, Well, if the first letter is always indicative, then just check that: data have; length email $200.; input email $; cards; XXX@GMMIL.COM YYY@GMIL.COM ZZZ@GMAILL.COM XXX@YAHOOO.COM YYY@YHOO.COM ZZZ@YAHOHO.COM XXX@HOOTMAIL.COM YYY@HAATMAIL.COM ZZZ@HITMAIL.COM ; run; data want; length newmail $200.; set have; select(substr(scan(scan(email,2,'@'),1,'.'),1,1)); when ('G') newmail=scan(email,1,'@')||"@GMAIL.COM"; when ('Y') newmail=scan(email,1,'@')||"@YAHOO.COM"; when ('H') newmail=scan(email,1,'@')||"@HOTMAIL.COM"; otherwise newmail=""; end; run;

RW9 · ‎07-14-2014

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

RW9 · ‎07-14-2014

I second Reeza's approach. If you absolutely have to do it this way, then get all your data, calculations done and export to excel with basic proc export. Have a intermediary Excel with some VBA code which will populate the template anyway you like with the data you have exported. DDE may not be supported much longer. One further option you may want to consider is to move your template to XLSX format. This is the latest MS Office XML based format. Once you understand how it fits together (for instance rename an XLSX to .zip and then you can open and see folders/files beneath), you can pretty much build it to look however you like, as long as you know XML, and Open Office format.

RW9 · ‎07-11-2014

Yes, check what your standard macro is outputting for the numerb of pages. It sounds like the macro is telling you there are three pages, hence that code appears three times. Word, when it reads it in however is saying for my page settings I think this should be over five pages and is filling the information out for you, Office being helpful again! Maybe also check what assumptions your standard macro uses, maybe you are print to a smaller page size, or landscape versus portrait. Even a slightly larger font could throw the calculation out. Hope you get it sorted, page numbering is a real pain.

RW9 · ‎07-11-2014

Part one could be done with SQL pretty easily: proc sql; select A.* from HAVE A left join (select distinct ACCOUNT_ID,count(distinct PROD_ID) as CNT from HAVE group by ACCOUNT_ID) B on A.ACCOUNT_ID=B.ACCOUNT_ID where CNT > 1; quit; For pattern matching, you could try retain statement (or lag): data want; set have; retain lstprod_id; by account_no; if first.account_no then lstprod_id=prod_id; if lstprod_id=1 and prod_id=2 then ...; run;

RW9 · ‎07-11-2014

Ha, this follows on from your other question. Why not just assign a flag within a datastep for each group: data ...; set ...; array flags{2} $1.; if nouv_date < '01JUN14'd then flag{1}="Y"; else flag{1}="N"; if nouc_date < '01MAY14'd then flag{2}="Y"; else flag{2}="N"; run; Then you can use these flags later on as a where clause for each processing.

RW9 · ‎07-11-2014

I would suggest that this condition: if week_id= &start_wk then do; doesn't resolve at any point to be true, hence the call symput is not run and ID_START is never defined. The likelihood is that the call: %let startwk =201425; %let endwk =201428; %bci_period(&startwk,&endwk); when looked in combination with the if statement resolves to: if week_id=201425 then do; Which doesn't work. If your sure that week_id is character and contains a value for 201425, then put quotes around the macro variable and retry: if week_id="&start_wk." then do; This should then find a row. However if week_id is numeric then you may need to convert it to the numeric type of week_id, e.g. if put(week_id,yymm6.) = "&start_wk." then do;

RW9 · ‎07-11-2014

I believe that function operates across variables, this problem is across rows per variable. Also there are > 5000 variables so couldn't dynamically put them into a macro variable. Plus the possibility that just one row may be missing then the next not, so only min of last block of missing.

RW9 · ‎07-11-2014

No probs. I forgot to actually include the code on my post: data d; attrib id format=$20. name format=$20. dob format=ddmmyy10. informat=ddmmyy10. sal format=best. desig format=$20.; infile cards delimiter=','; input id name $ dob sal desig $; cards; 100,krishna chandran,21/09/1989,5000,assistant manager 101,hareesh menon,10/05/2010,8000,engineer run;

RW9 · ‎07-11-2014

Sorry, yes I should have replaced that: options noquotelenmax; data _null_; set WORK.LOOP; call execute('proc sql; insert into WORK.RESULTS set VARIABLE="'||strip(name)||'", FIRST_DATE=(select MIN(BASE.NOUV_DATE) format=date9. from WORK.HAVE BASE where BASE.'||strip(name)||' is null and not exists(select distinct THIS.'||strip(name)||' from WORK.HAVE THIS where THIS.NOUV_DATE > BASE.NOUV_DATE and THIS.'||strip(name)||' ne .)); quit;'); run; options quotelenmax;

RW9 · ‎07-11-2014

Whoah, it gets worse, 5000 variables! Anyways, just create a dataset with the list of variables (from sashelp.vcolumn) and then use that in your data _null_ step as the loop instead of the do + array: data have; attrib nouv_date format=date9. var91001_t0 var91059_t0 format=best.; nouv_date='01jan13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01feb13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01mar13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01apr13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01may13'd; var91001_t0=.; var91059_t0=.; output; nouv_date='01jun13'd; var91001_t0=.; var91059_t0=.; output; nouv_date='01jul13'd; var91001_t0=.; var91059_t0=.; output; run; proc sql; create table WORK.RESULTS ( VARIABLE char(20), FIRST_DATE num format=date9. ); create table WORK.LOOP as select NAME from SASHELP.VCOLUMN where LIBNAME="WORK" and MEMNAME="HAVE" and NAME ne "NOUV_DATE"; quit; options noquotelenmax; data _null_; set WORK.LOOP; call execute('proc sql; insert into WORK.RESULTS set VARIABLE="'||strip(name)||'", FIRST_DATE=(select MIN(BASE.NOUV_DATE) format=date9. from WORK.HAVE BASE where BASE.VAR91001_t0 is null and not exists(select distinct THIS.VAR91001_t0 from WORK.HAVE THIS where THIS.NOUV_DATE > BASE.NOUV_DATE and THIS.VAR91001_t0 ne .)); quit;'); run; options quotelenmax;

RW9 · ‎07-11-2014

In your proc report (assuming sorted): proc report data=...; columns a b c d e f; define a / 'ColA'; define b / 'ColB'; define c / group order=internal; /* this is the real part - use group or order */ define d / group order=internal For more guidance: http://support.sas.com/resources/papers/proceedings11/090-2011.pdf

RW9 · ‎07-11-2014

Eew, horrible data. The below will create a results table which, for each variable mentioned in the vname arrays, create one row with variable name and the date where missings started and didn't stop. data have; attrib nouv_date format=date9. var91001_t0 var91059_t0 format=best.; nouv_date='01jan13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01feb13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01mar13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01apr13'd; var91001_t0=8900; var91059_t0=9200; output; nouv_date='01may13'd; var91001_t0=.; var91059_t0=.; output; nouv_date='01jun13'd; var91001_t0=.; var91059_t0=.; output; nouv_date='01jul13'd; var91001_t0=.; var91059_t0=.; output; run; proc sql; create table WORK.RESULTS ( VARIABLE char(20), FIRST_DATE num format=date9. ); quit; options noquotelenmax; data _null_; array vnames{2} $200. ("VAR91001_T0","VAR91059_T0"); do i=1 to 2; call execute('proc sql; insert into WORK.RESULTS set VARIABLE="'||strip(vnames{i})||'", FIRST_DATE=(select MIN(BASE.NOUV_DATE) format=date9. from WORK.HAVE BASE where BASE.VAR91001_t0 is null and not exists(select distinct THIS.VAR91001_t0 from WORK.HAVE THIS where THIS.NOUV_DATE > BASE.NOUV_DATE and THIS.VAR91001_t0 ne .)); quit;'); end; run; options quotelenmax;

RW9 · ‎07-11-2014

There's no inbuilt function to do that anywhere in SAS as far as I know. The reason is simple, SAS is a structured data format, where there are columns with headers and data down the page. Excel has the concept of anything goes which is its biggest plus and negative point. What you are talking about is not a "Title", but text somewhere in the data region. If you could provide a small example of what you want, you could manipulate your data to contain this. For example if I have: A B C 1 2 3 4 5 6 7 8 9 And I want some text in cell D2, then I would update my dataset as: A B C D 1 2 3 4 5 6 Some text. 7 8 9 Then in my report, set the label for variable D to be missing: proc report data=...; column a b c d; define a / 'sfafsa'; ... define d / ''; run; Tagset quick guide: Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

RW9 · ‎07-11-2014

And if you don't like SQL; proc sort data=sashelp.vcolumn out=work.tmp; by descending varnum; where libname="SASHELP" and memname="CARS"; run; data _null_; set work.tmp (obs=1); call symput('LAST_NAME',name); run; data work.tmp; set sashelp.cars (keep=&LAST_NAME.); run;

Re: How to import excel with guessingrow function

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Re: Deleting rows in Excel with SAS

Re: PROC SGPLOT one command to produce graph for each case? And, quest...

Re: Deleting rows in Excel with SAS

Re: put statement and line break using /

Re: SAS code

Re: How to read from pdf to SAS

Re: Removing last word with length 1 or 2

Re: How to read from pdf to SAS

Re: How to read a macro variable value and convert it in SAS data set

Re: proc format in permanent library

Re: Copy all tables beginning with certain keyword from one lib to oth...

Re: Automated jobs failing because SAS logs contains Warning messages.

Re: Error: BY variables are not properly sorted on data set

Re: Difference between itt and safety populations

Re: ods excel -multiple sheets

Re: Lag function help

Re: ERROR: User does not have appropriate authorization level for file...

Re: Multiple graph in one page

Maxims of Maximally Efficient SAS Programmers

Re: data correction (mail id conversion using SAS)

Re: proc sql query to extract value out of xml data

Re: DDE - Populating an EXCEL Templage

Re: Determining page numbers in RTF

Re: Iterate through variables to identify a change

Re: blocking the values after a specific date

Re: ERROR: A character operand was found in the %EVAL function or %IF ...

Re: knowing when there are only missing values

Re: how can i get the date while i using named input format??

Re: knowing when there are only missing values

Re: knowing when there are only missing values

Re: SAS 9.4: Distinct values to be shown

Re: knowing when there are only missing values

Re: Disable columns header exporting in named ranges with PCFILES

Re: Conditionally executing a data step based upon last variable name ...

SAS Global Forum 2018