cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Vince28_Statcan
‎07-02-2019
  • 338 Posts
  • 11 Likes Given
  • 1 Solutions
  • 199 Likes Received

Re: Proc Transpose

by Quartz | Level 8 in SAS Procedures
‎08-22-2013 12:23 PM
‎08-22-2013 12:23 PM
I don't exactly like the resulting code compared to the SQL approach but here is how this SQL logic could be duplicated with data step syntax only. DSTEP3 dataset is identical to STEP2 dataset in the sql. From there, you can use the appropriately obtained indate/outdate with incnt to do the proc transpose /* Step1 do a full cartesian product of multiple into multiple - too bad SAS merge can't do that... got to use hash tables or other syntaxically difficult methods. Difference between dates were also added with the same approach as the SQL case approach */ data dstep1; length id 8. indate 8. incnt 8. outdate 8. outcnt 8.; if _n_=1 then do; declare hash myhash(dataset: 'have(where=(flag="OUT") rename=(time=outdate cnt=outcnt))', multidata: "YES"); myhash.defineKey('id'); myhash.defineData('outdate', 'outcnt'); myhash.defineDone(); end; set have(where=(flag="IN") rename=(time=indate cnt=incnt)); if myhash.find() = 0 then do;   if outdate-indate<0 then diff=99999999;   else diff=outdate-indate;   output;   do while(myhash.find_next()=0);   if outdate-indate<0 then diff=99999999;   else diff=outdate-indate;   output;   end; end; format indate datetime18. outdate datetime18.; drop flag; run; proc sort data=dstep1; by id outcnt diff; run; /* step2 */ /* Keep only the best time difference for each outcnt */ data dstep2; set dstep1; by id outcnt; if first.outcnt then output; run; /* Step3 */ /* Rebuild the missing incnt created by step2 */ data dstep3; length id 8. indate 8. incnt 8. outdate 8. outcnt 8.; if _n_=1 then do; declare hash myhash(dataset: "dstep2"); myhash.defineKey('id', 'incnt'); myhash.defineData('outdate', 'outcnt'); myhash.defineDone(); end; set have(where=(flag="IN") rename=(time=indate cnt=incnt)); rc=myhash.find(); if outdate=99999999 then outdate=.; output; drop flag rc; run; Vincent ... View more

Re: Proc Transpose

by Quartz | Level 8 in SAS Procedures
‎08-22-2013 11:34 AM
‎08-22-2013 11:34 AM
A not so simple alternative to achieve desired results: options datestyle=DMY; data have; input @1 id 3. @5 flag $3. @9 time anydtdtm15. @25 cnt 2.; format time datetime18.; datalines; 101 IN     04Sep1989:7:30    1 101 IN     04Sep1989:13:45  2 101 IN     21SEP1989:17:55 3 101 OUT 05SEP1989:7:15   1 101 OUT 22SEP1989:06:00 2 ; run; proc sql; create table step1 as select t1.id, t1.time as indate format=datetime18., t1.cnt as incnt, t2.time as outdate format=datetime18., t2.cnt as outcnt from have(where=(flag="IN")) as t1 left join have(where=(flag="OUT")) as t2 on t1.id=t2.id group by t1.id, t2.cnt having (t2.time-t1.time GT 0) and (t2.time-t1.time=min(                (case                when t2.time-t1.time LT 0 then 99999999                else t2.time-t1.time                end                )               )          ) ; create table step2 as select coalesce(t1.id, t2.id) as id, coalesce(t1.indate, t2.indate) as indate format=datetime18., coalesce(t1.incnt, t2.incnt) as incnt, t1.outdate format=datetime18., t1.outcnt from step1 as t1 full join     have(where=(flag="IN") rename=(time=indate cnt=incnt)) as t2 on t1.id=t2.id and t1.indate=t2.indate and t1.incnt=t2.incnt; quit; This pre-processes your data to provide the best in/out pairs and you can then use proc transpose with flag and incnt to name your new variables. The 9999999 was simply to assign a large enough date value when the difference is negative that the case would never be the result of the minimum summary function. The only possible issue that I can see is if the very last in/out pair has a missing value for out. In this case you may need to add an additionnal step to transform back date 9999999 (whatever this resolves to) to a missing value Vincent ... View more

Re: Extracting Data From Webpages

by Quartz | Level 8 in SAS Programming
‎08-20-2013 08:00 AM
3 Likes
‎08-20-2013 08:00 AM
3 Likes
Yes, the natural escape to substrings and scans and conditioning is the use of Regular Expressions via the PRXsomething functions and call routines. Or, if your website target offers webservices, you could use soap and read the file as xml and use a custom map/soap request to achieve the results I can't quite help you with regex syntax unless you provide either your targetted strings/substrings or somehow provide the logic of your chain of substr functions. But typically, you can create a single regex that can achieve all of your conditions for the substr chain and only extract one piece at a time based on the PRX results using a single substr statement, looping on your text using PRXNEXT However, if your web pages always have exactly the same format, it might be best to preprocess the data. Like there should be some obvious markup tags that distinguish where your relevant data is found and you'd likely save a lot of time using the markup languages strenght. Even moreso if the URL respects xml html in which case you can probably just strip a header line off of your URL file and read the whole thing as an XML with the XML engine and go directly to the tag of interest with a custom map. Long story short, many different options are availible depending on how your target website is setup. Using the power of xml is the one that will cause the fewer headaches like having to debug the regex or the substring chain logic but not all websites are using xml standards. ... View more

Re: Multiplying arrays without using a loop

by Quartz | Level 8 in SAS Programming
‎08-19-2013 09:18 AM
‎08-19-2013 09:18 AM
Hi Kavindra, You can use _NUMERIC_ to define an array referencing all numeric variables in the data vector and then loop on that array. data want;      set have;      array temp {*} _NUMERIC_;      if name in ("A", "B") then do;           do i=1 to dim(temp);                temp{i}=temp{i}*100;           end;      end;      drop i; run; It's not quite "without arrays" but it read to me as though your issue was that of getting all of your numeric variables in an array when not knowing all the variables names or, well, not wanting to hard type them all because they are not ranges. I have to agree with Jaap in that you shouldn't restrain yourself not to use arrays. They often provide the easiest solution and figuring out the appropriate syntax to solve your loop issue is what the SAS community/documentation is there for. However, as a possible alternative to obtaining all of your numeric variables names, you can always consider using SASHELP.vcolumn automatic table. Either load the name of the numeric variables in a macro for a proc datasets rename or just to use these macros to build the list of variables for an array statement for example. Vince Eh apparently I got beat to it by Rick while writting up sorry for duplicate code. ... View more

Re: How to import datetime ddmmyyyy hh:mm:ss informat

by Quartz | Level 8 in SAS Enterprise Guide
‎08-16-2013 11:38 AM
1 Like
‎08-16-2013 11:38 AM
1 Like
Hrm I'm not too familiar with what all options can be played directly with EG interface but here's an alternative that should allow you to only set the option and use the import interface as you did before options ->custom code->insert custom sas code before task and query code (Check) -> edit... type in a single line options datestyle=dmy; Save and then run whatever you were running. ... View more

Re: How to import datetime ddmmyyyy hh:mm:ss informat

by Quartz | Level 8 in SAS Enterprise Guide
‎08-16-2013 11:08 AM
‎08-16-2013 11:08 AM
options datestyle=dmy; data have2; input v1 anydtdtm.; put v1 datetime18.; datalines; 01/12/2012 09:24:54 ; run; output to log is 01DEC2012:09:24:54 So it should solve your problem. Vince ... View more

Re: How to import datetime ddmmyyyy hh:mm:ss informat

by Quartz | Level 8 in SAS Enterprise Guide
‎08-16-2013 11:03 AM
‎08-16-2013 11:03 AM
If anydt does read the value but fails to properly distinguish months from days, it likely has to do with your DATESTYLE= option. However, anydt. may or may not work with all datetime informats. Try options DATESTYLE=dmy; before your data step and see if it fixes your issue. Vincent ... View more

Re: Modify string in HTML file

by Quartz | Level 8 in Graphics Programming
‎08-16-2013 08:24 AM
3 Likes
‎08-16-2013 08:24 AM
3 Likes
Could you provide a more specific example that fails? Just copy like one line with at least one occurence of what you are attempting to replace. At first glance, it looks like %str or %nrstr paired with appropriate use of the following examples that can be found in %nrstr documentation Notation Description Example Quoted Value Stored %' unmatched single quotation mark %let myvar=%str(a%'); a' %" unmatched double quotation mark %let myvar=%str(title %"first); title "first %( unmatched open parenthesis %let myvar=%str (log%(12); log(12 %) unmatched close parenthesis %let myvar=%str (345%)); 345) Should achieve your desired results without needing to modify the macro defined by Dr Zhu. Something like %let old_str=%str(Mike said:%"Hey); %let new_str=%str(Mike whispered:%"Halt); Should appropriately prevent the unbalanced quote error when dereferencing old_str/new_str in the macro Vincent ... View more

Re: Proc template and multiway contingency tables

by Quartz | Level 8 in ODS and Base Reporting
‎08-14-2013 02:24 PM
‎08-14-2013 02:24 PM
Excellent thank you very much, very good explanations. ... View more

Re: Proc template and multiway contingency tables

by Quartz | Level 8 in ODS and Base Reporting
‎08-14-2013 01:58 PM
‎08-14-2013 01:58 PM
Hi Ballardw, Indeed it did what I wanted to achieve. May I ask what exactly the n=''*f=f6.0 does? I mean I understand that it turns into the frequency count but I'm trying to figure out how/what/when/where this syntax comes from and I haven't found it yet reading through proc tabulate documentation seeking for it. Many thanks, Vince ... View more

Re: DATASTEP summary

by Quartz | Level 8 in SAS Programming
‎08-14-2013 01:12 PM
‎08-14-2013 01:12 PM
proc sql;      select max(documentation_invalid), documentation_vv, max(occupancy_invalid), occupancy_vv, max(property_invalid), property_vv, max(purpose_invalid), purpose_vv      from have      group by documentation_vv, occupancy_vv, property_vv, purpose_vv; quit; you could use sum instead of max if you would want to count the number of time a row was "invalid" for your group instead of just figuring out if it ever was. It depends on what the data represents and what you ought to do. Vince ... View more

Re: join on 3 columns

by Quartz | Level 8 in SAS Procedures
‎08-14-2013 09:34 AM
‎08-14-2013 09:34 AM
If you are talking about DBailey's solution, it is because for each row of acct_dtl, the full subquery (select * from cons3 where home=t1.tlnbr or busi=t1.tlnbr or alt=t1.tlnbr) is recomputed so you are more or less doing a full query on table B for each row in table A. If your desired output was what he provided, my hash table solution should achieve the desired results in significantly less time. It's less pretty because of the long/dense hash object syntax but its of great use for efficiency. The only issue is that since all hash objects are in memory, if table B has millions of records, you might face an insufficient memory error. Vince ... View more

Re: join on 3 columns

by Quartz | Level 8 in SAS Procedures
‎08-14-2013 08:25 AM
‎08-14-2013 08:25 AM
I'm not sure to fully understand your requirement between the statement below and your current code orientation: I would like to join table A with Table B on three different columns and any matching records from Table B should be deleted in table A. In the below code I've written the records in data set NEW should be removed from Cons3. how can i do it? Is your desired result a new table with all the records in A that did not exist in B? Is your desired result a new table with the joined records AND an update to the table A to remove records that were in B? Because conceptually if you join A and B and then remove all records that were in B, you don't need a join to achieve this result since all of the "outer" portion of the join will have no values in the columns provided by table B. Anyway, if your objective is simply to reduce table A by removing all records such that a given key (acct_dtl) were in neither of the 3 varibles (home, busi, alt) on table B, it can be achieved without a join as follow: data updated_A;      length home $20. busi $20. alt $20.; /* You will need to adjust these lengths to be representative of the 3 variables in table B */      if _n_=1 then do;      declare hash homehash(dataset: 'cons3');      homehash.defineKey('home');      homehash.defineDone();      declare hash busihash(dataset: 'cons3');      homehash.defineKey('busi');      homehash.defineDone();      declare hash althash(dataset: 'cons3');      homehash.defineKey('alt');      homehash.defineDone();      end;      set acct_dtl;      if homehash.find(key: tlnbr) = 0 or busihash.find(key: tlnbr) = 0 or althash.find(key: tlnbr) = 0 then ; /* If any return code from find method is 0, then tlnbr was found in at least one of the lookup tables and                                                                       we want it not to be output thus the empty then statement */      else output; run; If this is not your desired output and that scott's solution does not help either, you should consider posting sample data and your desired final output(s). Vince ... View more

Re: Proc template and multiway contingency tables

by Quartz | Level 8 in ODS and Base Reporting
‎08-14-2013 08:04 AM
‎08-14-2013 08:04 AM
I forgot to mention, if it is possible to achieve with proc template on crosstabs, if anyone could suggest good documentation with a detailed tutorial, that would be much appreciated. I can have statscan library order the book if we don't have it on site especially since ultimately, a template code that achieves this result could very well be shared on our network since "sadly" contingency tables are extremely frequent in our clients' requirements. Apparently I can't edit the original post. Remove the , , delimiters for the data have;  step, I changed my mind in syntax half way through. Sorry. ... View more

Proc template and multiway contingency tables

by Quartz | Level 8 in ODS and Base Reporting
‎08-14-2013 08:01 AM
‎08-14-2013 08:01 AM
Hi, I have a requirement to produce a series of multi-way contingency tables (2-3-4 maybe even 5 way if it is interpretable) to introduce a possible logistic model with many more than 5 variables. Since this will eventually take the form of a document to our clients, it will likely be done through ods pdf, rtf or html so I am somewhat flexible to that extent although I would like it to be as automated as possible so that any change in the data would require only to rerun the program and maybe to very minimal manual work beyond this. I'm basically looking at the following: data have;      input var1 $1. var2 $1. outcome $1.;      datalines; 0,0,0 0,0,1 0,1,0 0,1,1 1,0,0 1,0,1 1,1,0 1,1,1 ; run; /* Where each record would repeat a different frequency number of times and could possibly have more than just 2 classes but that is irrelevant to explaining what I wish to achieve */ ods pdf file="c:\temp.pdf"; proc freq data=have;      table var1*var2*outcome / LIST; run; ods pdf close; The output generated is of a form similar to the table below. var1 var2 outcome=0 outcome=1 var1=0 var2=0 1 1 var1=0 var2=1 1 1 var1=1 var2=0 1 1 var1=1 var2=1 1 1 However, in litterature and which is also what I want to achieve, the outer variables (left most columns) values are normally only repeated when they change and not for every possible row. So my desired output would be something similar to var1 var2 outcome=0 outcome=1 var1=0 var2=0 1 1 var2=1 1 1 var1=1 var2=0 1 1 var2=1 1 1 I am looking for a way to achieve something similar in SAS. I'm absolutely not familiar with proc template and don't know if any other procedure (proc surveyfreq or proc tabulate maybe?) could achieve my desired output without playing with proc template. Nontheless, I am looking for the community help at the bare minimum to know if it is worth investing the time to learn proc tabulate for crosstabs (aka is it possible to achieve this output via proc template on crosstabs?). I believe I could achieve the desired output with heavy dataset manipulations and a proc print but it would be atrocious to double transpose the output from proc freq and blank out fields and whatnot for a job that may very well produce about a dozen 2-way tables, 66 3-way tables and (4 in 12) 4-way tables etc. Is it possible to do the above manipulation to the basic crosstabs output with proc template? Are there alternative procedures that could achieve the desired output? Are there additionnal options or suboptions on the table / LIST statement that could result in the same formating? Anything else that does not involve parsing the tables and using DDE to blank the appropriate fields? Thanks Vince ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎07-02-2019 05:06 PM
Likes given to
Likes from
Quick links