11-20-2017
H4xc1ty
Fluorite | Level 6
Member since
05-23-2013
- 20 Posts
- 0 Likes Given
- 0 Solutions
- 4 Likes Received
-
Latest posts by H4xc1ty
Subject Views Posted 4303 11-20-2017 09:33 AM 4371 11-19-2017 05:34 PM 4411 11-19-2017 03:30 PM 4424 11-19-2017 03:09 PM 1079 11-28-2016 05:10 PM 6098 06-23-2015 01:24 PM 6266 06-23-2015 12:22 PM 69035 10-14-2014 06:11 PM 71495 10-14-2014 04:55 PM 1181 06-25-2014 02:36 PM -
Activity Feed for H4xc1ty
- Posted Re: How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-20-2017 09:33 AM
- Posted Re: How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-19-2017 05:34 PM
- Posted Re: How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-19-2017 03:30 PM
- Posted How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-19-2017 03:09 PM
- Tagged How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-19-2017 03:09 PM
- Tagged How do I accomplish a dynamic "transpose" with multiple variables? on SAS Programming. 11-19-2017 03:09 PM
- Posted How do I send dynamic "where" criteria via passthrough query on SAS Programming. 11-28-2016 05:10 PM
- Got a Like for Re: Comparing a row in a dataset with the next row. 11-24-2016 04:53 AM
- Posted Re: Pass-through query to SQL Server takes a long time to run... on SAS Enterprise Guide. 06-23-2015 01:24 PM
- Posted Pass-through query to SQL Server takes a long time to run... on SAS Enterprise Guide. 06-23-2015 12:22 PM
- Got a Like for Comparing a row in a dataset with the next row. 10-29-2014 09:08 AM
- Got a Like for Re: Comparing a row in a dataset with the next row. 10-16-2014 01:34 PM
- Got a Like for Re: Comparing a row in a dataset with the next row. 10-14-2014 09:03 PM
- Posted Re: Comparing a row in a dataset with the next row on SAS Programming. 10-14-2014 06:11 PM
- Posted Comparing a row in a dataset with the next row on SAS Programming. 10-14-2014 04:55 PM
- Posted Re: Random Sample Question on SAS Enterprise Guide. 06-25-2014 02:36 PM
- Posted Random Sample Question on SAS Enterprise Guide. 06-25-2014 02:06 PM
- Posted Re: SurveySelect Question... on SAS Procedures. 06-04-2014 02:21 PM
- Posted Re: SurveySelect Question... on SAS Procedures. 06-04-2014 02:05 PM
- Posted SurveySelect Question... on SAS Procedures. 06-04-2014 01:38 PM
-
My Liked Posts
Subject Likes Posted 1 10-14-2014 04:55 PM 3 10-14-2014 06:11 PM
11-20-2017
09:33 AM
YES!!! This is exactly what I need!!! Thanks so much! Also, I offer my apologies to everyone if I wasn't being clear or providing enough information. It was not my intention to be vague. I appreciate all of the responses greatly!
... View more
11-19-2017
05:34 PM
I wish that were the case, but doing that breaks for me. As I mentioned earlier, I've seen that macro before and have been playing with it to get it to do what I want. Thus far though, no joy. Thanks for chiming in though.
... View more
11-19-2017
03:30 PM
See, those links are exactly what I was talking about when I was saying that all of my searches weren't demonstrating what I wanted to do. All of those links, which incidentally, I'd already reviewed, transpose the data such that one of the variables from the original dataset ends up as part of the output dataset header. I don't want that at all. I want to keep the original header test and just append the iteration number. For instance, if my headers were ID,A,B,C,D,etc... I'd be looking to have that become ID,A_1,B_1,C_1,D_1,A_2,B_2,C_2,D_2,etc...
If I am not being too clear, I apologize. I am choosing my words as best I can.
... View more
11-19-2017
03:09 PM
I put the word transpose in quotes in the title because I'm not totally sure that that is the 100% correct term for what I'm trying to accomplish. Most of the (large number) of results for my searches on the subject are performing the transpose in such a way that part of the data becomes the output dataset header, which is not my goal.
Essentially, what I want to achieve is to take the input dataset :
strUniqID
intNonUniqID
strAddress
dteAppDate
intAppNum
A12345
1
123 45th St
1/2/1970
47
A12345
2
234 56th St
3/4/1978
39
B12345
3
345 67th St
8/6/1982
35
B12345
4
456 78th St
12/31/1994
22
and manipulate it so that the output looks like :
strUniqID
intNonUniqID_1
strAddress_1
dteAppDate_1
intAppNum_1
intNonUniqID_2
strAddress_2
dteAppDate_2
intAppNum_2
A12345
1
123 45th St
1/2/1970
47
2
234 56th St
3/4/1978
39
B12345
3
345 67th St
8/6/1982
35
4
456 78th St
12/31/1994
22
I don't know if proc transpose can accomplish this and all of the macro examples I've seen out there don't quite fit what I'm trying to do. Any input would be MOST appreciated!!
Many thanks!!
... View more
11-28-2016
05:10 PM
Ok, so the answer to this question may very well have been poseed here or on the intarwebz somewhere, but I can't find it, so I'm submitting it.
A lot of the programs on which I work use SAS to connect to a SQL Server db and retrieve data. We do not have a SAS server. We just use the local. As such, we tend to write proc sql blocks to run as much query logic on SQL Server as possible and then pull in the smallest a result set possible to the local SAS workspace for post processing that utilizes SAS specific features.
Often we have a known set of information that we can send via the passthrough query to improve performance and efficiency. Things like lists of customer ids for example are common. For such lists we've used macro variables in the passthrough, but frequently, this does not work because the contents being fed to the macro variables might exceed the macro variable character limit. That results in truncation and broken programs.
I'm sure there is a better way to do this and have been searching fo a long time to find it. Thus far I've come up empty.
I hope there are some among you that have run into this before and might be able to shed some light.
Thanks!!
... View more
06-23-2015
01:24 PM
There are a couple of issues here. I assume that when you say "My SQL is identical", you're referring to the contents of your "SQL CODE SENT..." block. However, you are doing several other things here: 1. You're sorting your result set by four variables; I do this because event though I include it in my passthrough SQL Enterprise Guide (or proc sql, not sure which) will automatically reorder data by column order if I don't 2. You're transferring the result set from your physical database server to your physical SAS server; It is probably worth noting that we don't have an actual SAS server we use Enterprise Guide with the Local Server. 3. You're loading the data into "work.output_dataset" on your physical SAS server. Probably, your delay lies in one of these factors. It seems odd that you don't include your "order by" clause in your pass through SQL. (see above) If you do, does it make a difference to your Management Studio times? (no) During the 11 minutes, do you know for a fact that your database server is chugging hard on your request (therefore steaming your DBA), or could the database server be fairly quiescent? Is your result dataset bigger than 20 GB? Anything under that shouldn't present major problems. Tom
... View more
06-23-2015
12:22 PM
4.3 As the title states, I have a program in a process flow that pulls in data from SQL Server via a pass-through query. The problem is that it takes a LOT longer to run in Enterprise Guide than in SQL Server Management Studio. My SQL is identical. In Management Studio it runs in 1 minutes 17 seconds. In Enterprise Guide I kill it after 11 minutes (before I get an angry call or email from our DBA). Since the actual SQL submitted to SQL Server isn't the issue, I'm posting only the SAS-specific code. Hopefully someone can help. I have searched everywhere and am posting because I have exhausted other options. %let dsnname = ODBC_DATA_SOURCE_NAME; proc sql; connect to odbc as dw (dsn="&dsnname" uid=USERNAME pwd=PASSWORD); create table work.output_dataset as select distinct * from connection to dw ( ......SQL CODE SENT TO SQL SERVER....... ) order by column1 ,column2 ,column3 ,column4; disconnect from dw; quit;
... View more
10-14-2014
06:11 PM
3 Likes
WOW!! This worked perfectly!! Thank you so much! I never would have arrived there without your help.
... View more
10-14-2014
04:55 PM
1 Like
Good Day, I'll start with a little background. I have a dataset like the following : Patient_ID Admit_Date Disch_Date Facility_Type Flag 12345 02JAN2014:00:00:00.000 05JAN2014:00:00:00.000 Nursing Home 12345 05JAN2014:00:00:00.000 07JAN2014:00:00:00.000 Hospital 23456 02JAN2014:00:00:00.000 06JAN2014:00:00:00.000 Nursing Home 23456 06JAN2014:00:00:00.000 09JAN2014:00:00:00.000 Hospital 23456 08JAN2014:00:00:00.000 11JAN2014:00:00:00.000 Nursing Home 23456 11JAN2014:00:00:00.000 19JAN2014:00:00:00.000 Hospital 23456 19JAN2014:00:00:00.000 23JAN2014:00:00:00.000 Nursing Home 23456 24JAN2014:00:00:00.000 25JAN2014:00:00:00.000 Hospital 23456 25JAN2014:00:00:00.000 30JAN2014:00:00:00.000 Nursing Home 34567 09JAN2014:00:00:00.000 15JAN2014:00:00:00.000 Nursing Home 45678 13JAN2014:00:00:00.000 20JAN2014:00:00:00.000 Nursing Home etc.... I would like to loop through this dataset and update the Flag column with a 1 depending on information in the next row. The logic I need to use is something like the following : I = 1 do while I <= "num rows in dataset" If Patient_ID[I+1] = Patient_ID and Facility_Type[I+1] = "Hospital" and Facility_Type = "Nursing Home" and datepart(Admit_Date[I + 1]) >= datepart(Admit_Date) and datepart(Admit_Date[I + 1]) <= datepart(Disch_Date) Then Flag = 1 Else Flag = 0 I = I+1 Next This would update the dataset to be the following : Patient_ID Admit_Date Disch_Date Facility_Type Flag 12345 02JAN2014:00:00:00.000 05JAN2014:00:00:00.000 Nursing Home 1 12345 05JAN2014:00:00:00.000 07JAN2014:00:00:00.000 Hospital 0 23456 02JAN2014:00:00:00.000 06JAN2014:00:00:00.000 Nursing Home 1 23456 06JAN2014:00:00:00.000 09JAN2014:00:00:00.000 Hospital 0 23456 09JAN2014:00:00:00.000 11JAN2014:00:00:00.000 Nursing Home 1 23456 10JAN2014:00:00:00.000 19JAN2014:00:00:00.000 Hospital 0 23456 19JAN2014:00:00:00.000 23JAN2014:00:00:00.000 Nursing Home 0 23456 24JAN2014:00:00:00.000 25JAN2014:00:00:00.000 Hospital 0 23456 25JAN2014:00:00:00.000 30JAN2014:00:00:00.000 Nursing Home 0 34567 09JAN2014:00:00:00.000 15JAN2014:00:00:00.000 Nursing Home 0 45678 13JAN2014:00:00:00.000 20JAN2014:00:00:00.000 Nursing Home 0 etc.... I have been going nuts trying to figure out how to code this in SAS. There's tons of documentation online that sort of applies, but nothing specific enough and try as I might, I can't seem to work it out. I've been tempted to export the data to a file, parse it using VBS and then reimport, but that is horribly backwards and inefficient. Plus, I won't learn anything about SAS. Any help would be greatly appreciated. Thanks, John
... View more
06-25-2014
02:36 PM
Below is the code in its current state. how can it be updated that I can get an even number of strata (right now AllLOC=Prop and I am getting 9 records for one strata and 1 for the other due to source proportion. I would like even break out so in this case with N=10 it would be 5 each. The below is specific to one dataset that has enough records to choose from but some of the other datasets do not. _CLIENTTASKFILTER = status = 'Approved'; SORT DATA=WORK.MMC_FHP( WHERE=(status = 'Approved') ) OUT=WORK.SORTTempTableSorted; BY benefit_plan; ; SURVEYSELECT DATA=WORK.SORTTempTableSorted OUT=WORK.MMC_FHP_APPROVAL_SAMPLE METHOD=SRS N=10 SEED=1 NOPRINT; STRATA benefit_plan / ALLOC=PROP; ; ; _CLIENTTASKFILTER;
... View more
06-25-2014
02:06 PM
Utilizing proc Survey select I am trying to get a random sample of 10 records per dataset I have created. Each data set has been created based on specific criteria. Unfortunately we will be running qtrly and each data set will not always have 10 records to choose from and in some instances may have not data at all. How can I update my code to select N=10 and when there are less than 10 records it pull all available? Within specific datasets the random sample needs to include a Strata Variable. I would also like to have 10 records pulled total but broken out evenly between the strata, it is currently pulling proportionally.
... View more
06-04-2014
02:21 PM
jwillis : I have already read through the documentation, that's why I am asking here stat@sas : The macro program won't do, because I need whatever number of records are in the dataset if less than 20. Just putting a message that says there are not enough is not an option.
... View more
06-04-2014
02:05 PM
SELECTALL does not seem to fit this situation. I need a maximum of 20 rows. If I'm not understanding, please explain.
... View more
06-04-2014
01:38 PM
Good Day, I am trying to use a SurveySelect to select N records from several datasets. I need 20 records, but some of the datasets sometimes have less, which breaks the proc. I seem to recall a way to account for this and simple select the maximum number of records when N <20, but can't remember how to code it. here is the proc : PROC SURVEYSELECT DATA=WORK.SORTTempTableSorted OUT=WORK.Random_Sample METHOD=SRS N=20; STRATA Type LOB / ALLOC=PROP; RUN; QUIT; Any help is greatly appreciated. -John
... View more
06-02-2014
04:00 PM
Hi Tom, Thanks for the reply. Yes, I love using SAS and Enterprise Guide. I'd never even seen it until April of 2013 but having previous experience with other programming languages and reporting tools, it wasn't as much of a headache as I thought it would be. The biggest selling point is the community. Soooooo much info is available. The alternate options you reference are intriguing, but unfortunately not possible for us at this time. It's pretty ridiculous, in my opinion, but we do not have a SAS server. We've been using the local server for everything and mapping a folder on a file server to maintain a persistent dataset library. I keep trying to explain the performance benefits of getting a dedicated server, but so far, no dice. I will continue investigating using conditionals as Chris suggested. Thanks, John
... View more