About ballardw

ballardw

No, but then I had zero, nada, no idea of how you expected to use the values because you have not shared that. However you can use a data step to create code to execute that way. @kajal_30 wrote: so you are suggesting below ? Proc sql; select * from sashelp.car_summary_cats('dt_',put(id, id_to_value.)); quit; expecting it to resolve as below Proc sql; select * from sashelp.car_summary_2412; quit; Either of those SQL results are poor as they are sending the output to the results window. Not very useful. If you can build a string with the code such as : data junk; length longstring $ 2000; longstring = "Proc sql; select * from sashelp.car_summary_"||put(-1, id_to_value.)||';quit'; run; Then you can use Call Execute to use the code in the Longstring, OR write the statements to a text file with a .SAS extension, not needed but helpful later, and %include that file which will treat the contents a code and execute. If you have a data with with ID values that might look like: data _null_; /* this has values of ID in it*/ set somedataset; length longstring $ 2000; longstring = "Proc sql; select * from sashelp.car_summary_"||put(id, id_to_value.)||';quit'; call execute (longstring); run; Which would execute that Proc Sql call for every value of ID in the data set. I would think actual sql would likely have a "Create table " perhaps using that same put(id, id_to_value.) as part of the output data set name data _null_; /* this has values of ID in it*/ set somedataset; length longstring $ 2000; longstring = "Proc sql; Create table mylib.output_"||put(id, id_to_value.)|| "as select * from sashelp.car_summary_"||put(id, id_to_value.)||";quit"; call execute (longstring); run;

ballardw

Could you show us the code you are using to "loop over years"? It may be possible to insert a check before you use it. Are there actually multiple variables involved?

ballardw

@kajal_30 wrote: Exactly because I am trying a workaround and as I already clarified that I have values which I need to use to concatenate with other value to create a table name just a simple 1 line ask have a value -2 associated to another value 1234 so I need to use 1234 in the table name that's it. So, how does this not meet the need, using the format created above: Tablename = cats('dt_',put(id, id_to_value.));

ballardw

Are you asking "Why when I change the values / variables used in the model does one of the output statistics change?" If the goal is to overlay the graph curves and not generate a third model with different data then use ODS OUTPUT to capture the data used to show the graphs. proc logistic data=ROCmerge plots(only)=roc ; where stroke_time <= 10 ; model instroke(event='1') = BASESTROKEMODELRISKPCT / outroc=Baseroc ; output out=BASEpredict pred=BASEPROB; ods output fitplot=basefitplot; run; This will create data set with the information used for most of the graph. The variables _xcont1 and _predicted have the x,y coordinates of the basic prediction (ROC) curve. Create similar data set for the other model. Combine them adding a variable to identify which model is the source to use as a GROUP variable and use Sgplot to plot the _xcont1 and _predicted series. data toplot; set basefitplot (in=inbase) fullfitplot (in=infull) ; if inbase the Plot='Base'; else if infull then Plot='Full'; run; proc sgplot data=toplot; series x=_xcont1 y=_predicted /group=plot; run; The variables _lclm and _uclm could be used with a BAND plot for the Lower and Upper variables as well.

ballardw

That is not a very clear description of how you are attempting to use this. Any place you have the variable ID you can retrieve the text Tablename = cats('dt_',put(id, id_to_value.)); And throwing around macro variables with values, where they are assigned or context doesn't clarify much. Show the values you need, where you need them. What you start with. And we may be able to get there. Assuming 'this won't work' without a clear description of what it is to be used for results in poor suggestions. And if this related to the https://communities.sas.com/t5/SAS-Programming/Need-help-with-the-code-for-fetching-data-from-different/m-p/944602#M370093 post then why haven't you responded to suggestions there?

ballardw

The function you want to "offset" dates, datetimes and time values is INTNX. You provide an interval, the base date, datetime, or time value, the number to "offset" and optionally an alignment parameter to return the Begining, End, Middle or "Same" alignment. "Same" would be such as incrementing by months but what the returned day of the month to be the "same" as the starting date. Common intervals for dates are :day, week, month, year. If you want to increment a Datetime value with those then interval starts with DT to let SAS know that you are using a datetime value: dtday, dtmonth, dtyear for example. You want to read up as there are some options with multiples such as Year2 is biennial and shifts such as Year.3 (note the period) that treats March 1 (third common subperiod or month for year) as the start. So to increment a datetime value : intnx('dtdays', variable,100). If you wanted the same time of day: Intnx('dtday', variable,100,'S'). 'E' would return a time portion of 23:59:59 (one second before midnight). You can get the FORMAT or INFORMAT of a variable using the functions VFORMAT or VFORMATX and VINFORMAT and VINFORMAT Something like this will get format, or informat information and variable names if that's what you're looking for instead of proc contents. Most of the time when I hear "thousands of variables" my first though is why. data something; set somedataset (obs=1); array v (*) _numeric_ ; do i=1 to dim(v); formatname = vformat( v[i]); variablename= vname(v[i] ); end; So you could use code in side that Do loop to increment the value of the variable v[i] based on the value of Formatname. data have; input id dttm number date; format dttm datetime14. date mmddyy10.; datalines; 77 1668138559 92345 22265 107 1678148579 78456 22337 923 1964135557 28940 22340 ; run; data want; set have; array v (*) _numeric_ ; do i=1 to dim(v); formatname = vformat( v[i]); if formatname =: 'DATETIME' then v[i] = intnx('dtday',v[i],100,'S'); if formatname =: 'MMDDYY' then v[i] = intnx('day',v[i],100,'S'); end; drop i formatname; run; 'S' doesn't really mean much for days but sometimes having it is nice when you decide to change this to 3 months, for example. Then just change day to month and 100 to 3. If you haven't used it before the =: is a "begins with" comparison. So you don't have to include the actual length of the format. You can use this with the IN operator in case you need to search for YYMMDD, DATE or DDMMYY formats (or informats)

ballardw

Pretty much any time you are doing a conversion from numeric to text, which is what the macro language is, text, you need to consider how SAS does that. If you don't explicitly control the conversion, such as with PUT and a format and possibly an alignment indicator, you get a generic routing that uses a BEST12. format, resulting in leading spaces if your value uses fewer digits. I can't come close to remembering how many times I may have used trim(left(<somevariable>)) when building macro variables. Before Call SymputX was added to the statements.

ballardw

@kajal_30 wrote: Is it possible to create format in sas without using hardcoded values but values from columns ? Yes. You have to create a data set with the needed values in the for specific variable names that the procedure expects to see. data one; infile datalines; input ID value ; datalines; -1 1234 -2 4567 -3 7890 -4 4321 ; run; data fmtmaker; set one; fmtname='Id_to_Value'; type='N'; start=Id; label=Value; run; proc format library=work cntlin=fmtmaker; run; proc print data=one; var id; format id id_to_value.; run; There are some elements a bit tricky about ranges of values the Other option in proc format for what to do with not listed values and whether you actually need a FORMAT or an INFORMAT.

ballardw

@xxformat_com wrote: The swimmer plot is an example where type= option could be used in keylegend. In this paper you can start to see that the code starts to get messy with so many scatter statement. https://www.lexjansen.com/phuse-us/2018/dv/DV08.pdf Here we have to keep in mind that programs have to be validated in clinical research and programmers don't really like validating other people programs. So it is essentiel to have short effective and comprehensive code to facilitate the process. The SAS system offers the opportunity to derive items from a specific symbol plotted on the graph. If we don't have any flexibility to define the symbol size, then the benefit of the feature is lost and we keep with old workaround. What process? I am still not sure what you even want. Sometimes "messy" plot code comes because the programmer doesn't want to restructure the DATA. Sometimes the code is messy because someone is making a very complicated graph. Sometimes the code in a white paper is to show the possibilities and not always the best way to accomplish one specific task. So, can to start over and describe what the graph you are concerned with, not the ones in the paper, but your graph is supposed to show?

ballardw

How exactly do expect to use that not-legally named variable? This smells like trying to put way too much data into macro variables.

ballardw

@alepage wrote: Hello, I trying to use if and else if condition to define macro variables varname and varname2 but I am unable to check their value. Does someone can help me with that issue. data _null_; set inforcelisting5; call execute (compbl(cat( "Libname dssource ", engine,'"',path,'";', '%nrstr(%macro lookuptbl;)', '%nrstr(%if %varexist(',strip(fname),',police) %then %do; %let varname=police;%end;)', '%nrstr(%else %if %varexist(',strip(fname),',agreement_nbr) %then %do; %let varname=agreement_nbr;%end;)', '%nrstr(%else %if ',strip(cie),' eq be %then %do; %let varname2=datechea;%end;)', '%nrstr(%mend lookuptbl;)', '%nrstr(%lookuptbl;)', '%nrstr(%put &=varname &=varname2;)', "Libname dssource clear;" ))); run; partial log file: NOTE: CALL EXECUTE generated line. 1 + Libname dssource base"/dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/ "; NOTE: Libref DSSOURCE was successfully assigned as follows: Engine: BASE Physical Name: /dwh_actuariat/sasdata/sas2002/be/habi/bel.prod1000.sah1stat.dec2002.data/ 1 + %macro lookuptbl;%if %varexist(polices,police) %then %do; %let varname=police;%end;%else %if %varexist(polices,agreement_nbr) %then %do; %let 2 The SAS System 13:22 Thursday, September 19, 2024 2 + varname=agreement_nbr;%end;%else %if be eq be %then %do; %let varname2=datechea;%end;%mend lookuptbl;%lookuptbl;%put &=varname &=varname2;Libname dssource clear; WARNING: Apparent symbolic reference VARNAME not resolved. WARNING: Apparent symbolic reference VARNAME2 not resolved. varname varname2 NOTE: Libref DSSOURCE has been deassigned. After cleaning up the log text so it looks a bit more like nice code I get this: %macro lookuptbl; %if %varexist(polices,police) %then %do; %let varname=police; %end; %else %if %varexist(polices,agreement_nbr) %then %do; %let varname=agreement_nbr; %end; %else %if be eq be %then %do; %let varname2=datechea; %end; %mend lookuptbl; %lookuptbl; %put &=varname &=varname2; Libname dssource clear; Which tells me the reason the variables do not exist where you use the %put at the end because they are currently only LOCAL in scope to the macro %lookuptbl. You can fix this by adding something inside the definition of the macro that generates: %global varname varname2 ; I really don't see much reason for this macro definition to be in call execute statements though. Perhaps provide some PARAMETERS to the macro definition instead of recompiling it with different values of Fname and Cie in the body of the macro. Maybe something like %macro lookuptbl (val1=, val2=; %if %varexist(&val1.,police) %then %do; %let varname=police; %end; %else %if %varexist(&val2.,agreement_nbr) %then %do; %let varname=agreement_nbr; %end; %else %if be eq be %then %do; %let varname2=datechea; %end; %mend lookuptbl; Then you would just have to call the macro with the val1=fname, val2=cie . If you are naming the library Dssource because you have hard coded the library name into whatever that %varexist macro might be you have also likely made a poor style choice for that macro.

ballardw

One way that works with your provided examples. Note no MACRO code needed in this approach. data dataset_2401; input IDNUM RANK; datalines; 1 12 2 13 3 14 4 15 5 16 ; data dataset_2402; input IDNUM RANK; datalines; 1 21 2 22 3 23 4 24 5 25 ; data dataset_2403; input IDNUM RANK; datalines; 1 31 2 32 3 33 4 34 5 35 ; data dataset_2404 ; input IDNUM RANK; datalines; 1 41 2 42 3 43 4 45 5 46 ; data dataset_2405; input IDNUM RANK; datalines; 1 51 2 52 3 53 4 55 5 56 ; data tomerge; set dataset_2401 - dataset_2405 indsname=inset; length date $ 4; /* this pulls the "date" from the data set name*/ date = scan(inset,2,'_'); run; data master; input idnum date :$4.; datalines; 1 2401 2 2402 3 2403 4 2404 5 2405 ; proc sort data=master; by idnum date; run; proc sort data=tomerge; by idnum date; run; data want; merge master (in=inmaster) tomerge ; by idnum date; if inmaster; run; This is one of the approaches that may not extend well to millions of observations The Set statement can use lists of various forms. The one shown is a sequential list. If you want all of the datasets that start with "dataset_" you could use "Set dataset_: ;" The colon immediately following the name tells SAS to use all the sets whose names start with "dataset_". One simple caveat wit the above: the bit that parses the data set name from the Indsname option would need to have a different number in the Scan function if the library name contains any _ or the set names contain more than one _.

ballardw

Is that variable Date and actual SAS date value, a simple number value or character? Information about that variable is likely to be very critical to specifics on matching. I believe it has mentioned that example data should be provided in the form of working data steps so that we do not have to guess as to what type of variable is involved. How many datasets? How many Idnum values? Some approaches that works quickly for 5 sets with 5 id values may not work in a timely fashion for 100 data sets with millions of Ids.

ballardw

Could you please share the code you are using to generate the report so can see exactly how you are attempting this underline. Also share which ODS destination you are sending the output to. For example if attempting to use the HEADLINE option in Proc Report that will only work for the ODS LISTING destination. So reports sent to ODS RTF, PDF or HTML will not show the underline. I believe there are a few other approaches to underlining that only work for Listing or specific ODS destinations.

ballardw

Awful ugly code. I am going to go out on a limb and after reformatting part of that code to: IF 61 IN Q AND 31 NOT IN Q AND 1 NOT IN Q AND BILLING_CYCLE_DAY = 15 THEN DO; ALLOCATION_DATE = INPUT(SUBSTR(VNAME(Q[WHICHN(61,OF Q[*])]),5),DATE7.); OPENING_DPD = 61; TAGGING = 'BUCKET 2 NON NPA'; END; IF ((61 IN Q AND 32 IN Q) OR (31 IN Q)) AND 1 NOT IN Q AND BILLING_CYCLE_DAY = 15 THEN DO; ALLOCATION_DATE = INPUT(SUBSTR(VNAME(Q[WHICHN(31,OF Q[*])]),5),DATE7.); OPENING_DPD = 31; TAGGING = 'BUCKET 1 NON NPA'; END; IF 1 IN Q AND 61 NOT IN Q AND 31 NOT IN Q AND BILLING_CYCLE_DAY = 15 THEN DO; ALLOCATION_DATE = INPUT(SUBSTR(VNAME(Q[WHICHN(1,OF Q[*])]),5),DATE7.); OPENING_DPD = 1; TAGGING = 'BUCKET X NON NPA'; END; guess that this is the line: ALLOCATION_DATE = INPUT(SUBSTR(VNAME(Q[WHICHN(31,OF Q[*])]),5),DATE7.); The conditions that get to this line start with ((61 IN Q AND 32 IN Q) OR (31 IN Q)) Which means 61 and 32 may be there but there is no requirement for 31 to be there. So for some observation with that true then Whichn(31,of q(*)) is returning 0, which would be the specific subscript that is out of range. One really hopes your code doesn't look at posted in the question or you will be wasting lots of time trying to decipher "line number" in general because you have places several statements on one line making the diagnostics much less useful.

Online Status	Online
Date Last Visited	4 hours ago