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: 
piddy
‎09-16-2016
  • 10 Posts
  • 2 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Allocation of libery - %lib(libery name) - get is working with how...

by Fluorite | Level 6 in SAS Programming
‎09-02-2016 06:20 AM
‎09-02-2016 06:20 AM
Hi again   In my current macro I also have incorporated to use %lib() to access SQL databases. See below. I tried to make it work with the macro new macro you provided but I couldn't figure it out.   The macro below works fine except the thing about I want the user to be able to write the libery name in the () as they want. So either lowcase, upcase etc.  As the macro is now it only excepts UPCASE because that's how I have written them in the code.     %macro lib(lib);   %let program=\\ipt202wp.iplocal.dk\f-drev; %put &program;   %let AdHoc = '&program\adhoc'; %let afstindb = '&program\Afstemning\Afst_indbetal'; %let analyse = '&program\analysedata';   /*************************************************************** **************************ALLOCATION OF LIBERY'S***** ****************************************************************/   /**Local data libery's**/   %if "&lib" = "ADHOC" %then libname adhoc "&program.\adhoc"; %else   %if "&lib" = "AFSTINDB" %then libname afstindb "&program.\Afstemning\Afst_indbetal"; %else   %if "&lib" = "ANALYSE" %then libname analyse "&program.\analysedata"; %else   /************ALLOCATION OF ALL LIBERYS IN ONE CALL***********/   %if "&lib" = "AKTUARY" %then %do; %lib(ADHOC); %lib(AFSTINDB); %lib(ANALYSE);   %end; %else   /****************ALLOCATION OF SQL LIBERY'S******************/   %if "&lib." = "WFSAP" %then libname WFSAP ODBC complete ="DRIVER={SQL server}; server=SERVERPATH\NAME; Database=DATABASENAME; Trusted_Connection=yes;"; %else   %if "&lib." = "ISS" %then libname ISS ODBC complete ="DRIVER={SQL server}; Server=SERVERPATH\NAME; Database=DATABASENAME; Trusted_Connection=yes; User id=; Password=; ";   /************ALLOCATION OF ALL SQL-LIBERYS IN ONE CALL************/   %if "&lib" = "SQL" %then %do; %lib(WFSAP); %lib(ISS); %end; %else     %put *************** REFERENCE &lib IS UNKNOWN - TRY AGAIN ***************;   %mend;   ... View more

Re: Allocation of libery - %lib(libery name) - get is working with how...

by Fluorite | Level 6 in SAS Programming
‎09-01-2016 11:03 AM
‎09-01-2016 11:03 AM
Thanks. So however the user writes the name of the libery(WFSTAT,wfstat,Wfstat etc.) your solution converts all input in the %lib() to lowcase so there will be no hickups when running the code. Is that understood correcly? ... View more

Re: Allocation of libery - %lib(libery name) - get is working with how...

by Fluorite | Level 6 in SAS Programming
‎09-01-2016 10:35 AM
‎09-01-2016 10:35 AM
I the top of a program where I allocate a libery, normally you could would write this for example:   libname wfstat '\\ipt202wp.iplocal.dk\f-drev\wfstat';    With the macro in my post I can just write:   %lib(WFSTAT);   My question is how can I adjust my code so I both can write   %lib(WFSTAT); %lib(wfstat); %lib(Wfstat); or how ever it can be spelled.   ... View more

Re: Allocation of libery - %lib(libery name) - get is working with how...

by Fluorite | Level 6 in SAS Programming
‎09-01-2016 10:21 AM
‎09-01-2016 10:21 AM
It's a Windows server running SAS. ... View more

Allocation of library - %lib(library name)

by Fluorite | Level 6 in SAS Programming
‎09-01-2016 10:04 AM
‎09-01-2016 10:04 AM
Hi everybody   I hope you can help me with a question. At my work I have made a macro to allocate all of our data library's with the macro, %lib(name of library ). See below for just one of these libraries:   In my current solution based upon the code below I allocate the library with writing: %lib(ADHOC). After macro has been put in production there has been a wish in the department to be able to write ADHOC as either adhoc, Adhoc, ADHOC or how ever you can write it.   I know of the upcase, lowcase, propcase function but I am not sure how to implemet it in my code below.   I hope you can help me with this and many thanks in advance.    Kind regards Thomas   %macro lib(lib);   %let program=\\ipt202wp.iplocal.dk\f-drev; %put &program;   %let AdHoc = '&program\adhoc';   %if "&lib" = "ADHOC" %then libname adhoc "&program.\adhoc"; %else %put *************** REFERENCE &lib IS UNKNOWN - TRY AGAIN 🙂 ***************;   %mend; ... View more

Re: Macrowise variables in datastep

by Fluorite | Level 6 in SAS Programming
‎05-02-2016 05:35 PM
‎05-02-2016 05:35 PM
That's quite clever. 😀 Will use that. Have a deadline soon but will incoporate that later. Thank you a lot. As a side kick as a new beginner to SQL programming are there any books / websites you would recommend? ... View more

Macrowise variables in datastep

by Fluorite | Level 6 in SAS Programming
‎05-02-2016 02:19 PM
‎05-02-2016 02:19 PM
Hi everybody   The code below is running as it should, it just fills up a lot of space 🙂   I have 4 variables in which I use the function prxchange to make certain expression similar on all observations and for the 4 variables and then in the end use the CATX function to rearrange the final variables as I want them to be.   I would like to just write the code once, and not writing it 4 times because of the 4 variables. I hope someone can help me with that.   Thanks in advance.   %let kø =NEW_QUEUE; %put &kø; %let koe =QUEUE; %put &koe; %let trin =SAGSTRIN; %put &trin; %let strin =SAGSTRINNAVN; %put &strin;   data &navn (drop= &kø &kø._2 &kø._3 &kø._4 XREF_clean1 &koe &koe._2 &koe._3 &koe._4 XREF_clean2 &trin &trin._2 &trin._3 &trin._4 XREF_clean3 &strin &strin._2 &strin._3 XREF_clean4 rename =(&kø._5=NEW_QUEUE &koe._5=QUEUE &trin._5=SAGSTRIN &strin._4=SAGSTRINNAVN Ansvarlig=Ini);   set wfstat.sagstider (keep=&varliste); length &kø &kø._2 &kø._3 &kø._4 &kø._5 &koe &koe._2 &koe._3 &koe._4 &koe._5 &trin &trin._2 &trin._3 &trin._4 &trin._5 &strin &strin._2 &strin._3 &strin._4 $ 60; start_date =datepart(trin_start); slut_date =datepart(wf_slut); Md_afslut =month(slut_date); Year =year(slut_date);   *Changing XREF step (NEW_QUEUE), so it's the same for all oberservations; &kø._2 = left(prxchange('s/X-REF|NY XRF|Ny X-Ref|Ny XRef|X-ref|NXR|Ny XR/XREF/oi',-1,trim(upcase(&kø)))); &kø._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&kø._2)))); if prxmatch("m/XREF/oi",&kø._3) then XREF_clean1="XREF"; &kø._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&kø._3)))); &kø._5 = CATX(" - ",XREF_clean1,propcase(&kø._4));   *Changing XREF step (QUEUE), so it's the same for all oberservations; &koe._2 = left(prxchange('s/X-REF|NY XRF|Ny X-Ref|Ny XRef|X-ref|NXR|Ny XR/XREF/oi',-1,trim(upcase(&koe)))); &koe._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&koe._2)))); if prxmatch("m/XREF/oi",&koe._3) then XREF_clean2="XREF"; &koe._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&koe._3)))); &koe._5 = CATX(" - ",XREF_clean2,propcase(&koe._4));   *Changing XREF step (SAGSTRIN), so it's the same for all oberservationst; &trin._2 = left(prxchange('s/X-REF|NY XRF|Ny X-Ref|Ny XRef|X-ref|NXR|Ny XR/XREF/oi',-1,trim(upcase(&trin)))); &trin._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&trin._2)))); if prxmatch("m/XREF/oi",&trin._3) then XREF_clean3="XREF"; &trin._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&trin._3)))); &trin._5 = CATX(" - ",XREF_clean3,propcase(&trin._4));   *Changing XREF step (SAGSTRINNAVN), so it's the same for all oberservations; &strin._2 = left(prxchange('s/X-REF|NY XRF|Ny X-Ref|Ny XRef|X-ref|NXR|Ny XR/XREF/oi',-1,trim(upcase(&strin)))); if prxmatch("m/XREF/oi",&strin._2) then XREF_clean4="XREF"; &strin._3 = left(prxchange('s/XREF//i',-1,trim(upcase(&strin._2)))); &strin._4 = CATX(" - ",XREF_clean4,propcase(&strin._3)); format start_date slut_date date9.; run; ... View more

Re: Reduce datasteps in count and sum

by Fluorite | Level 6 in SAS Programming
‎05-02-2016 09:52 AM
‎05-02-2016 09:52 AM
Hi again   Your solution was correct. Thank you so much again. I made a mistake in dropping some variabels. I'm not so good at macros yet but how could I rewrite your code to a macro? ... View more

Re: Reduce datasteps in count and sum

by Fluorite | Level 6 in SAS Programming
‎05-02-2016 09:15 AM
‎05-02-2016 09:15 AM
HI freelanceReinhard   Thank you very much for your reply.   When I run your code there seems to be a problem with case_ID is doesn't count properly. Is it possible to rewrite it as a macro?     ... View more

Reduce datasteps in count and sum

by Fluorite | Level 6 in SAS Programming
‎05-02-2016 06:55 AM
‎05-02-2016 06:55 AM
Hi   I hope you can help me. This is the output from the code below and I was hoping there is a smart way to reduce the datasteps. There are other variable in the original dataset but the variables below are the ones important for the coding.   Thank you in advance.   OBJECT_ID Case_ID Case_count Case_count_sum 10104PROD21C4CE 1 1 2 10104PROD21C4CE 1 2 2 10201PROD230948 2 1 2 10201PROD230948 2 2 2 10207PROD2315A8 3 1 3 10207PROD2315A8 3 2 3 10207PROD2315A8 3 3 3 10280PROD23FEB7 4 1 2 10280PROD23FEB7 4 2 2 10294PROD242336 5 1 2 10294PROD242336 5 2 2 10300PROD242B33 6 1 2 10300PROD242B33 6 2 2 10300PROD242CE7 7 1 2 10300PROD242CE7 7 2 2 10302PROD243380 8 1 3 10302PROD243380 8 2 3 10302PROD243380 8 3 3 10347PROD2495CD 9 1 1 10347PROD2495E8 10 1 2 10347PROD2495E8 10 2 2 10349PROD249971 11 1 1 10356PROD24A468 12 1 2 10356PROD24A468 12 2 2 11005PROD24DB55 13 1 2 11005PROD24DB55 13 2 2 11006PROD24DEA6 14 1 3 11006PROD24DEA6 14 2 3 11006PROD24DEA6 14 3 3 11012PROD24ED64 15 1 2 11012PROD24ED64 15 2 2 11013PROD24F0D4 16 1 1 11013PROD24F0DA 17 1 2 11013PROD24F0DA 17 2 2 11017PROD24F6D6 18 1 2 11017PROD24F6D6 18 2 2 11024PROD250F13 19 1 1 11027PROD251616 20 1 2 11027PROD251616 20 2 2 11028PROD2518E3 21 1 2       data testm; set X; by OBJECT_ID; retain OBJECT_ID; if first.OBJECT_ID then Case_ID+1; run;   data testm2; set testm; by Case_ID; Case_count+1; if first.Case_ID then Case_count=1; run;   proc sort data=testm2; by Case_ID descending Case_count; run;   data testm3; set testm2; by Case_ID; retain Case_count_sum; if first.Case_ID then Case_count_sum=Case_count; output; run; ... View more

If string is macthed and number of obs. per bygroup > 1 then delete

by Fluorite | Level 6 in SAS Programming
‎04-30-2016 12:27 PM
‎04-30-2016 12:27 PM
Hi everybody   I hope you can help me. I have a dataset which depicts the case flow per ID/case.  Of this I am calculating the days from when a case step is starting to when it's ending of a case to measure how good the caseworker is performing.  You can follow the flow of a case by the variables SAGSTRIN, QUEUE and NEW_QUEUE and in that order. The meaning of an "ending" case step is where the case has been set to rest so to speak. I am only interested in the last step (new_queue) and have in a previous data step used the following if statement:   if index(trim(upcase(NEW_QUEUE)),"XREF") GE 1 AND index(trim(upcase(SAGSTRIN)),"XREF") NE 1;   where XREF is how a "ending case step" is spelled. Above data step is used to remove all other steps than XREF steps in the final new_queue. There can be multiple XREF step per ID and it's not all XREF step I am interested in. Let's say the case worker awaits information and the case is put to a XREF step until that information arrives. That shouldn't be included when determine the number of days a caseworker has used on a case. So looking through data I have seen a kind of systematic in how the structure us, so I can set up some rules for which XREF steps to be excluded per case/ID and it is this I would like some help with.   So far I don’t want a case completely removed, example if a case has 2 XREF steps with "”Dødvurd Mgl Opl Mod" from below is should only remove one so there is always one step left per case.   So below I have counted the cases pr. ID and a sum variable:   options obs=250; *Dannelse af en sum variabel som viser antallet af sager pr. ID; data case1; set &navn._død_m_XREF_u_vudr_dag_X2; by ID; ID_count+1; if first.ID then ID_count=1; run;   proc sort data=case1; by ID descending ID_count; run;   data case2; set case1; by ID; retain ID_count_sum; if first.ID then ID_count_sum=ID_count; output; run;   proc sort data=case2 out=outfil.sas7bdat;by ID ID_count;run;     Then I would like the following rules to apply:   If the step before new_queue contains "Dødvurd Mgl Opl Mod" and the sum of XREF steps>1 then the XREF step will be removed.          Then I run the code above again to count cases per ID and next rule: If the step before new_queue contains "Dødvurd Mgl Opl Ej Mod" and the sum of XREF steps>1 then the XREF step will be removed.          Then I run the code above again to count cases per ID and next rule: If the step before new_queue contains "Dødafv oplysninger" and the sum of XREF steps>1 then the XREF step will be removed.     I have tried with the following code but found that if a case has 2 instances of "Dødvurd Mgl Opl Mod" both will be removed. Because the sum is of course 2 and so it will delete both of them.   data case3; set case2; by ID ID_count; if first.id and prxmatch("m/Dødvurd Mgl Opl Modt/oi",QUEUE) and ID_count_sum>1 then delete; run;   I hope you can help me with the coding and maybe it can be done more simple. Thanks in advanced.   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-16-2016 08:44 AM
Likes given to
View All
Quick links