05-07-2018
Wouter
Obsidian | Level 7
Member since
06-23-2011
- 66 Posts
- 3 Likes Given
- 1 Solutions
- 5 Likes Received
-
Latest posts by Wouter
Subject Views Posted 4120 05-01-2018 04:46 AM 3977 05-01-2018 03:30 AM 4011 04-30-2018 12:09 PM 4032 04-30-2018 11:26 AM 1405 11-24-2013 05:03 AM 1507 11-23-2013 03:19 PM 2837 03-14-2013 11:43 AM 2837 03-14-2013 10:41 AM 4129 03-14-2013 09:28 AM 4129 03-14-2013 08:27 AM -
Activity Feed for Wouter
- Posted Re: SAS EG prompt - how to use in query builder? on SAS Enterprise Guide. 05-01-2018 04:46 AM
- Posted Re: SAS EG prompt - how to use in query builder? on SAS Enterprise Guide. 05-01-2018 03:30 AM
- Posted Re: SAS EG prompt - how to use in query builder? on SAS Enterprise Guide. 04-30-2018 12:09 PM
- Posted SAS EG prompt - how to use in query builder? on SAS Enterprise Guide. 04-30-2018 11:26 AM
- Got a Like for Re: How to create a 'simulation' with multiple automatic iterations?. 09-01-2015 04:24 AM
- Posted Re: How to create info based on an unknown imported dataset (no clue of # and kind of variables)? on SAS Programming. 11-24-2013 05:03 AM
- Posted How to create info based on an unknown imported dataset (no clue of # and kind of variables)? on SAS Programming. 11-23-2013 03:19 PM
- Got a Like for left outer join in Proc SQL. 03-14-2013 10:48 PM
- Got a Like for Re: left outer join in Proc SQL. 03-14-2013 02:55 PM
- Posted Re: Creating percentages in PROC REPORT on ODS and Base Reporting. 03-14-2013 11:43 AM
- Liked Re: Creating percentages in PROC REPORT for Cynthia_sas. 03-14-2013 11:43 AM
- Posted Re: Creating percentages in PROC REPORT on ODS and Base Reporting. 03-14-2013 10:41 AM
- Posted Re: left outer join in Proc SQL on ODS and Base Reporting. 03-14-2013 09:28 AM
- Posted Re: left outer join in Proc SQL on ODS and Base Reporting. 03-14-2013 08:27 AM
- Posted Re: left outer join in Proc SQL on ODS and Base Reporting. 03-14-2013 07:09 AM
- Liked Re: Creating percentages in PROC REPORT for Cynthia_sas. 03-14-2013 06:29 AM
- Posted Re: Creating percentages in PROC REPORT on ODS and Base Reporting. 03-14-2013 06:26 AM
- Posted Re: Creating percentages in PROC REPORT on ODS and Base Reporting. 03-14-2013 05:05 AM
- Posted Re: Creating percentages in PROC REPORT on ODS and Base Reporting. 03-14-2013 04:59 AM
- Posted left outer join in Proc SQL on ODS and Base Reporting. 03-14-2013 04:48 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 4 4 -
My Liked Posts
Subject Likes Posted 3 09-30-2012 03:00 PM 1 03-14-2013 04:48 AM 1 03-14-2013 09:28 AM
05-01-2018
04:46 AM
Yeah, got it now... Looks like a bug, or a very strange way to solve this (that is, in this version??). In the query, hit "Options". Now you'll see:
Output data:
Name: 'HHI_Cr_Institutions_&Rep_date'n
Label: HHI_Cr_Institutions_&Rep_date
Now, hit "Change", and hit "Cancel", and the "Name" will change, equal to the label. Now, click "OK" and don't run the query, just save it. From now on, it will pick up the macro variable...
... View more
05-01-2018
03:30 AM
Yes, at 2 places now; one at the start of the project (ticked: "use prompt value throughout project) and now one at the query builder in question itself. Same outcome.
Part of the log:
NOTE: Compressing data set WORK.test_&REP_DATE'n increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.'test_&REP_DATE'n created, with 26 rows and 5 columns.
... View more
04-30-2018
12:09 PM
Hi, thank you for your answer. I've created the 'prompt variable' at the beginning of the project. When I want it to use later in a query builder (see also the picture below): the output name is: WORK.HHI_Banks_&rep_date, and the output table is... indeed called WORK.HHI_Banks_&rep_date...
Prompt
First query in project where Rep_date is created by prompt
The query builder
... View more
04-30-2018
11:26 AM
All, I maybe do have a simple question... In EG, I've created a prompt ("rep_date") in which a user can enter some number (date). This date I want to use in a query builder, as part of the name of a new SAS table (see picture in the doc file).
However, the table created doesn't contain the text as entered by the user, but the name of the macro (so the output table is called WORK.Test_&rep_date in stead of something like WORK.Test_31JAN2018).
Is there a solution for this, or is it not possible to use a macro var this way? A text based prompt doesn't work either...
I'm on SAS EG 7.12.
Thanks for the response!
... View more
11-24-2013
05:03 AM
Yes, most likely that will be incorporated at the end of the code (or somtething similar). But for this, you *need* the variable names. And... these are unknown in 'my project'. So the question leaves us with: how to put in unknown (number of) variables into this PROC? Create a DO loop --> macro's?
... View more
11-23-2013
03:19 PM
Hi, I'm facing the following 'challenge': based on *any* imported dataset with an unknown number / set of variables (by proc import, or just simply dragged into Enterprise Guide) I want to create the following information among other things (as a report or output in a dataset): 1) The number of records filled and/or blanc (OBC_ID=_n_ creates a copy of obs, always filled, so that should be an easy step in the process) 2) Per variable (presented by variable name), the total sum of all records (in case of numeric field) 3) Per variable (presented by variable name), the ratio filled records vs. blanc records, so the % of the variable name that is blanc (for numeric and character variables) The dataset vcolumn in SASHELP contains all variables of the (random) imported set, but not the info as needed unfortunately. For this assignment, it looks like I need to create a loop in which all unknown variables are assigned to a macro, and from there the code should read out all records of the variable, so the output can be generated? Should that be a good approach? If so, how can this be done? For example, the output has to provide me with information as presented below (all variables of the imported dataset which contains unknown variables should be present, so the dummy dataset as presented as the desired output below does contain 7 variables): *************************** Output, based on a random imported set: Variable name | number of records in total dataset | total sum of records | % blanc (or % filled) Video_ID | 1600 | 1280800 | 100% Video_ID2 | 800 | 158766 | 50% Name | 1600 | N/A (Char) | 100% Address | 400 | N/A (Char) | 25% Amount | 1600 | 5500 | 100% Date_rent | 1600 | N/A (output=sum dates) | 100% Date_due | 1600 | N/A (output=sum dates) | 100% How can this be done...? There's no dataset in SASHELP with such info, if I'm correct. Thanks for your input!!
... View more
03-14-2013
10:41 AM
Hi, Thanks for your reply. I've renamed the original datafile (yeah, my employer is quite strict...) to xxx, so you could see 'xxx' as a datasource. That's all. The outcome of the file is exactly what I need, see the example (and the 'Percent by productionmonth of 201210 and the percenttotal, which are not 'blurred'). Only, the variable Bucket is mentioned twice (once as defined, as a text, but secondly as a number. I need this number indeed (a total of applications), but I can't change the header. Besides the part below, the variable Bucket is already defined so it can't be defined again. So I need the colomn which is now called "BUCKET" (since I need these numbers), but preferably with another header name. The file itself is also based on an example given by one of the white papers (how to present % in your Proc Report)...
... View more
03-14-2013
09:28 AM
1 Like
Got them all (followed all base courses --> knowledge (as reffered to above) + sheets), books for certified base programmer (passed), now going for the advanced level (also all books in house). But round about 20k of pages... Thought it would be a good idea to post the question here, since this is a forum for / with SAS experts. Saves me some time.
... View more
03-14-2013
08:27 AM
But there must be some program rules i guess...? In the SAS course I followed, this was one of the topics (SAS statements used in Proc SQL). Otherwise SAS would be one big trial and error game?
... View more
03-14-2013
07:09 AM
Partly 😉 Since I've learned that you can't use certain SAS statements in a PROC SQL session (and I remember one of them was LEFT join, because LEFT is already reserved for the SAS base language), I was wondering if it will work correctly. It seems to work, but then again, why does "LEFT" not turn blue in the "left outer join" statement?
... View more
03-14-2013
06:26 AM
Hi, One question again! 🙂 I've created the query which deliveres the desired output as wanted, but...below the column named 'BUCKET' the number of xxx is vissible (made all unreadable -> company data, except for 2 figures to give you an idea of what is was I wanted. Do you know how I can rename this column (so BUCKET = nr. of xxx)? Thanks in advance for your answer! I've used the following code: proc report data=xxx nowd out=outpct split='*'; column PRODUCTION_MONTH bucket Bucket=wtn percnt tpercnt; define PRODUCTION_MONTH / group ; define bucket / group format=buckets. 'Bucket*name' order=internal; define MONTH_ON_BOOK / n; define percnt / computed format=percent8.2 'Percent by *PRODUCTION_MONTH'; define tpercnt / computed format=percent8.2 'Percent*Total'; compute before; totcount = wtn; endcomp; compute before PRODUCTION_MONTH; count = wtn; endcomp; compute percnt; percnt= wtn/count; endcomp; compute tpercnt; tpercnt= wtn/totcount; endcomp; compute after PRODUCTION_MONTH; percnt= wtn/count; tpercnt= wtn/totcount; endcomp; break after PRODUCTION_MONTH / suppress summarize dol skip;
... View more
03-14-2013
05:05 AM
Hi, thank you for your extensive reply! The query I've used, was used before on another dataset. In this dataset, the percentages were already incorporated (2 columns). Right now, I'm dealing with another dataset which has not any percentages in it (I see now). The strange non-excisting connections (variables) are coming from the changes I've made to the code (since I'm dealing with company data / queries) --> these are not very useful in this one... I'm sorry for the inconvenience! I thank you for the link --> I'm going to change the query based on the new dataset which contains no extra info regarding %.
... View more
03-14-2013
04:59 AM
No, you're right. I've used an old code which uses a table in which the percentages are already incorporated (in 2 columns). Since I'm facing the problem of creating the same outcome with another dataset right now (which has not the computed percentages in it I now see), I have to come up with another solution. Since I'm dealing with work related data / queries, I have renamed the variables. But... not all of them supposedly. This explains the strange non-excisting contexts. Thanks for your extensive reply and your effort: I'm going to follow the link you've provided to get this going!
... View more
03-14-2013
04:48 AM
1 Like
I was wondering... I can remember some SAS statements can't be used in a Proc SQL session. Like "left outer join". After creating this code, the "left" (see below in red) is not coloured blue like the remaining part of the formula "... outer join". I thought this was one of the statements that did not work (since left has another function in SAS). But the code does work. Can anyone confirm that a "left outer join" just works within a proc SQL step? Thanks in advance! proc sql; create table dir.deel2 as select * from dir.deel1 t1 left outer join nl_xxx.yyy t2 on t1.accnr = t2.accbusid ; quit;
... View more