About user24feb

user24feb · ‎06-01-2015

I think the explicit pass-through and the macro-variable don't mix. Try something like this: %Let datasrc=Macro_datasrc; *!; %Let job_name=Macro_job_name; Data Command; Length Cmd $100.; Cmd="Proc SQL;"; Output; Cmd=Catt(" connect to SQLSVR as SQLDB (datasrc=","&datasrc.",");"); Output; Cmd=Catt(" select * from connection to SQLDB ( EXECUTE msdb.dbo.sp_start_job @job_name = '","&job_name.","');"); Output; Cmd=" disconnect from SQLDB;"; Output; Cmd="Quit;"; Output; Run; Data _NULL_; Set Command; Call Execute (Cmd); Run;

user24feb · ‎05-07-2015

Couldn't test, but I think: Data table2; If _N_ ge 2 Then Set table1(Firstobs=1 Keep=Category Var Rename=(Var=Var_Lag Category=Category_Lag)); Set table1; If Category eq Category_Lag Then Var=Min(Var,Var_Lag); Run;

user24feb · ‎05-06-2015

Maybe this approach is sufficient (or tells us what's missing): Data ru2014; Input id C2 C10_1 C11_1 C12_1 C13_1 C14_1; Datalines; 011039 2 1 2 2 2 2 011040 2 2 2 2 2 2 011042 2 2 2 2 2 2 011042 2 2 2 2 2 2 011044 2 2 2 2 2 2 ; Run; Proc SQL NoPrint; Select Catt(Name,'=S',Name) Into :Rename_List Separated By ' ' From (Select Name From Dictionary.Columns Where UpCase(Libname) eq "SAS_WORK" AND UpCase(MemName) eq "RU2014" And UpCase(Substr(Name,1,1)) eq "C"); * IMPORTANT: check your libname!!!; Quit; %Put **&Rename_List.**; Data Want; Set ru2014; Rename &Rename_List.; Run;

user24feb · ‎05-06-2015

If you have got time to play around a little. I am wondering if this is an option: data have1; input id; cards; 101 102 103 104 105 106 107 108 ; run; Data _NULL_; Length id 8.; Declare Hash H (Dataset:'have1',Multidata:'y'); H.Definekey('id'); H.Definedone(); Call Missing (id); Do i=101,104,105; * enter id's of have2 here; rc=H.Find(Key:i); rc=H.Remove(); end; rc=H.Output(Dataset:'Want'); Run; .. but RW9's solution seems to be better ..

user24feb · ‎05-04-2015

I believe you could mean this (count by group) 😉 Data Have; Input Nr @@; Datalines; 1 1 1 2 2 3 3 3 4 4 4 4 5 5 5 ; Run; Proc SQL; Create Table Want As Select a.Nr, Monotonic()-b.Min_Grp+1 As Rank From Have a Left Join (Select Nr, Min(Monotonic()) As Min_Grp From Have Group By Nr) b On a.Nr eq b.Nr; Quit;

user24feb · ‎05-04-2015

data have; input i column; cards; 1 0.5 2 0.6 3 0.7 4 0.8 ; run; Data _NULL_; Have_File=Open("have"); Obs=Attrn(Have_File,"Nobs"); Call SymputX ("Obs",Obs); Run; Data Want (Keep=i Col1-Col&Obs.); Array Col Col1-Col&Obs.; Set have; Do j=1 To Dim(Col); Col{j}=IfN(i le j,column,.); End; Run;

user24feb · ‎04-28-2015

It seems that you would like to calculate some kind if simple moving average (as Astounding described). However, if you simply would like to get the "i" attached to the lag function, you could try call execute: Data A (Drop=i); Do i=1 To 100; x=1; Output; End; Run; Data _NULL_; Call Execute ('Data B;'); Call Execute (' Set A;'); Do i=1 To 10; Call Execute (' s=Sum(s,lag'!!Compress(Put(i,2.))!!'(x));'); End; Call Execute (' Output;'); Call Execute ('Run;'); Run;

user24feb · ‎04-28-2015

You're looking for the modulo-function: mod(101234,100) gives 34.

user24feb · ‎04-24-2015

Hello, I'm playing around a little and it is obviously my first fedsql try. If this program works: Libname sqlserv ODBC Schema=DBO DSN=sassql; Proc SQL NoPrint; Create Table Test_A As Select Year_Month_Key, Sum(Sales_Group) As Sales_Group From sqlserv.vFact_Sales_Planning Where Year_Month_Key>=201401 Group By Year_Month_Key Order By Year_Month_Key; Quit; why do I get the following error with fedsql: Libname sqlserv ODBC Schema=DBO DSN=sassql; Proc FedSQL NoPrint; Create Table Test_A As Select Year_Month_Key, Sum(Sales_Group) As Sales_Group From sqlserv.vFact_Sales_Planning Where Year_Month_Key>=201401 Group By Year_Month_Key Order By Year_Month_Key; Quit; Log: ERROR: Table "SQLSERV.VFACT_SALES_PLANNING" does not exist or cannot be accessed ERROR: ERROR: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'Planning_Datamart.DOMXXXXX\XXX.VFACT_SALES_PLANNING'. ? Thanks&kind regards

user24feb · ‎04-22-2015

I understand you're playing around a little, but I am not sure, if this would be a proper approach. If your macro list is a tupel, this is, if you always have aa, bb, and cc combined, you could simply check, for example, if cc has a value at all with "%If (&cc^=) %Then ..", etc. %macro abc(aa=, bb=, cc=); %If (&aa.^=) & (&bb.^=) & (&cc.^=) %Then %Do; *execute something; %End; %Else %Do; %Put **something is missing**; %End; %mend abc; %abc(aa=mm,bb=nn) However, if your macro parameter list is dynamic, you might want to do the checking before you split your list up into individual elements.

user24feb · ‎04-22-2015

I would split into weekly and monthly too. Weekly version: Data A; Input Report $1. Date:DDMMYY10. Frequency $; Format Date Date9.; Datalines; a 01/01/2015 weekly a 15/01/2015 weekly a 22/01/2015 weekly b 03/02/2015 weekly b 10/02/2015 weekly b 17/01/2015 weekly c 10/01/2015 monthly c 12/02/2015 monthly c 10/04/2015 monthly c 12/05/2015 monthly ; Proc Sort Data=A; By Report Date; Run; Data B (Keep=Report Date: Missing_Report_Date); Length Report $1. Date 8; Length Date_Previous 8; Format Date_Previous Date9.; Length Missing_Report_Date 8; Format Missing_Report_Date Date9.; If _N_ eq 1 Then Do; Declare Hash H (Dataset:'A (Where=(Frequency="weekly"))',Multidata:'y',Ordered:'y'); Declare Hiter HI ('H'); H.Definekey('Report','Date'); H.Definedone(); End; Set A (Where=(Frequency="weekly")); rc=HI.First(); Do While (not rc); Date_Previous=Date; Report_Prev=Report; rc=HI.Next(); Delta=IntCK('week',Date_Previous,Date); If (Report_Prev eq Report) Then Do; Do i=1 To Delta-1; Missing_Report_Date=IntNX('week',Date_Previous,i,'same'); Output; End; End; End; Stop; Run;

user24feb · ‎04-14-2015

Comprehensive description of Windows Task Scheduler: http://support.sas.com/resources/papers/proceedings11/005-2011.pdf.

user24feb · ‎04-14-2015

Could be a case for a hash iterator, where you walk through the teacher absences and output if absence_date is between date_enrolled and date_left ..

user24feb · ‎04-14-2015

If I understood your program correctly, it is not what I was up to. In fact, my final code was very similar to Xia Keshan's (will set his answer to correct shortly). I hope the following code demonstrates, why I did not choose proc sql but a hash table: Data A; Input Prod Comp; Datalines; 1 33 1 44 2 11 2 55 2 66 ; Data B; Input Comp Subcomp; Datalines; 33 111 33 222 55 666 55 777 55 888 ; Proc SQL; Select A.Prod, A.Comp, B.Subcomp From A, B Where A.Comp eq B.Comp; Quit; Data Want (Drop=rc); Length Comp 8. Subcomp 8.; If _N_ eq 1 Then Do; Declare Hash H (Dataset:'B',Multidata:'y'); H.Definekey('Comp'); H.Definedata('Subcomp'); H.Definedone(); Call Missing (Comp, Subcomp); End; Do While (not Eof); Set A End=Eof; rc=H.Find(); If (rc) Then Do; Call Missing (Subcomp); Output; End; Do While (not rc); Output; rc=H.Find_next(); End; End; Run; Proc Print Data=Want; Run;

user24feb · ‎04-13-2015

Thanks! (Will take some time to work through ..)

Online Status	Offline
Date Last Visited	‎09-02-2022 10:58 AM

Re: Macros

Conditionally define a constraint with proc optmodel

Re: Define Constraint for Proc Optmodel

Define Constraint for Proc Optmodel

Re: data set for "simulation models .." by Erdman

data set for "simulation models .." by Erdman

Re: Saving Excel-dataset after modifying

Saving Excel-dataset after modifying

Re: Create and slice a sparse set of parameters in proc optmodel

Create and slice a sparse set of parameters in proc optmodel

Re: Conditionally define a constraint with proc optmodel

Re: Define Constraint for Proc Optmodel

Re: data set for "simulation models .." by Erdman

Re: PROC EXPORT to XLSX is creating a .bak file

Re: Saving Excel-dataset after modifying

Re: Passing a comma delimited variable to a Macro

Re: log summary is blank

Re: import excel from , skipping first 7 lines

Re: data set for "simulation models .." by Erdman

Re: Splitting a macro variable string into 2

Re: Data Integration Studio (Pass sas macro variable into SQL Server S...

Re: How can I create an ordered row with lag variable?

Re: How to add prefix to variable name?

Re: pass observations in data step

Re: How can I implement rank function in Proc sql?

Re: from column to triangle matrix

Re: Problem with lag function in do loop

Re: How to get the last 2 digits from a numeric value

FedSQL vs SQL

Re: Check Macro parameters

Re: How do I write a SAS program ?

Re: How to schedule my program between 8pm to 6am daily

Re: Array Processing (?)

Re: Get a full list of possible combinations of an asymmetric tree

Re: Run queries in parallel