FAQ Database Discussion Community


%DROPMISS - SAS

sas,sas-macro
I've been reading up on how to DROP variables from my dataset that have null values in every observation - it seems the best way to do this is using the %DROPMISS macro function - however I'm getting an error msg - below is the code I'm trying and the...

How to write a macro loop over months in YYYYMM format?

sas,sas-macro
I have 26 tables in my SAS work folder that goes with yearMONTH format that goes from 201301-201502 (i.e. 201301 to 201312, 201401 to 201412, 201501, 201502). I have to create 26 new tables, and join them (see sample code below for more details). How should I loop the data?...

SAS: Creating Tables Dynamically for different dates and table names in one go

sas,sas-macro,enterprise-guide
I'm stuck to something about creating tables dynamically by date filter. I have SAS code for setting date filters before running main codes. This it the date code; data _null_; /*ACTUAL DATES*/ R_act_beg=intnx('month',today(),-12,'beginning'); call symput('R_act_beg',R_act_beg); R_act_end=intnx('month',today(),-1,'end'); call symput('R_act_end',R_act_end); name_m=put(month(intnx('month',today(),-1)),z2.); call symput('name_m',name_m); name_y_act=put(year(intnx('month',today(),-1)),z4.); call symput('name_y_act',name_y_act);...

Writing a macro in SAS to create a table

sas,sas-macro
Very new to SAS Programming. Want to start with something simple - writing a macro that run an append query. This is all I have managed to figure out. Where am I going wrong? %MACRO APPENDTEST; PROC SQL; CREATE TABLE WORK.APPENDTEST AS SELECT * FROM WORK.MONTHLY_SALES_SUMMARY QUIT; %MEND APPENDTEST; ...

macro variable is uninitialized after %let statement in sas

sas,sas-macro
I want to create something in SAS that works like an Excel lookup function. Basically, I set the values for macro variables var1, var2, ... and I want to find their index number according to the ref table. But I get the following messages in the data step. NOTE: Variable...

How do I work out the data type of my macro variable in SAS

sas,sas-macro
How do I print out the data type of a macro variable in the log %macro mymacro(dt2); %LET c_mth = %SYSFUNC(intnx(month,&dt2.d,-1,e),date9.) ; %put &c_mth; %mend; mymacro('01sep2014') I have a bunch of macro variables assigned using a %let or into: my problem is I'm trying to do a bunch of boolean...

SAS Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

date,sas,sas-macro
I have a series of proc sql statements which pull data for Active, Inactive and Lapsed customers. I end up with 3 tables. *Customers_Active *Customers_InActive *Customers_Lapsed Active: 0-12M purchaser Inactive: 13-24M purchaser, did not purchase 0-12M (active day range minus 12 months) Lapsed: 25-36M purchaser, did not purchase 0-24M (inactive...

SAS: assign a quantile to a macro variable

sas,sas-macro
In SAS, how can I assign the 97.5% quantile of the normal distribution to the macro variable z? Not working 1 %let z = quantile("normal", 0.975); Not working 2 %let z = %sysfunc(quantile("normal", 0.975)); ...

Trying to create a macro which checks for a macro variable existence and creates it if doesn't exist

sas,sas-macro
%Macro symexistchk(valuex); %if %symexist(&valuex) %then %put &valuex; %else %do; %let valuex = 0; %end; %Mend symexistchk; %symexistchk(g900_r); I want to check if macro variable g900_r exist and create one if it doesn't exist. Thanks, Sam....

SAS: getting the filesize of created DBF file

stored-procedures,sas,sas-macro
I have SAS stored process that ceates DBF file from SAS data set rr_udf_value and finds its size (F_SIZE): filename dbfout "/SASInside/DBF/myfile"; proc export data=rr_udf_value outfile=dbfout dbms=dbf replace; run; %let f_nm=/SASInside/DBF/myfile.DBF; %let rc=%sysfunc(filename(onefile, &f_nm.)); %let fid=%sysfunc(fopen(&onefile)); %let F_SIZE=%sysfunc(finfo(&fid,File Size (bytes))); %put &F_SIZE; The problem is that the variable F_SIZE is...

SAS sorts my macro variable values

sas,sas-macro
I'm trying to do some calculations based on different currencies where the output dataset consists of a column with company names and the following columns are the sum for each company in each specific currency (So one sum column per currency). I want to do the calculations through a loop...

SAS MACRO: Create many datasets -modify them - combine them into one within one MACRO without need to ouput multiple datsets

sas,sas-macro
My initial Dataset has 14000 STID variable with 10^5 observation for each. I would like to make some procedures BY each stid, output the modification into data by STID and then set all STID together under each other into one big dataset WITHOUT a need to output all temporary STID-datsets....

What's wrong with these macro parameters?

sas,sas-macro
I have the following simplified version of a piece of code that I am working on: %macro test(var); %if &var = 'Sub Prime' %then %do; %let var2 = 'Sub_Prime'; %put &var2; %end; %mend; %test(Sub%str( )Prime); Basically the point of this is that if var = 'Sub Prime' that var2 should...

Execute Macro inside SQL statement

sql,macros,sas,sas-macro
The Situation: I have a table mytable with two columns: tablename and tablefield: |-----------|------------| | tablename | tablefield | |-----------|------------| | table1 | id | | table2 | date | | table3 | etc | |-----------|------------| My core objective here is basically, make a Select for each of these tablenames,...

Skipping by 2 in a MACRO

sas,sas-macro
I want to skip by two datasets in the following macro: %macro bulk_ODI_extract(low_id,high_id); %do loop_match=&low_id %to &high_id; %ODI_commentary(&loop_match,2); %end; %mend bulk_ODI_extract; %bulk_ODI_extract(749781,749787); So the final dataset should include 749781, 749783, 749785 and 749787, while skipping 749782, 749784 and 749786. ...

SAS how to use a macro variable as a date

sas,sas-macro
I have a global macro variable from another macro which looks like '01jan2014' when you print it in the log i.e. there are enforced quotemarks I want to use this in a proc sql statement but I can't as it doesn't like the variable type. How do I convert this...

Sas Macro to semi-efficiently manipulate data

sas,sas-macro
Objective: Go from Have table + Help table to Want table. The current implementation (below) is slow. I believe this is a good example of how not to use SAS Macros, but I'm curious as to whether... 1. the macro approach could be salvaged / made fast enough to be...

How do I use a value that is returned from a Macro Variable in another macro

sas,sas-macro
I have simplified this a lot so it can be repeated %macro macro_one(dt2); %let var1 = &dt2; %mend; Then I have another macro and I want to use the output from macro one in macro 2 %macro macro_print(dt2); /*call macro 1*/ %macro_one(&dt2); %put &var1; %mend; /call macro/ %macro_print('purple'); It should...

Extend SAS MACRO to multiple fields

sas,sas-macro
I have a macro inspired by "PROC SQL by Example" that finds duplicate rows based on a single column/field: data have ; input name $ term $; cards; Joe 2000 Joe 2000 Joe 2002 Joe 2008 Sally 2001 Sally 2003 ; run; %MACRO DUPS(LIB, TABLE, GROUPBY) ; PROC SQL ;...

Why is end parenthesis not closing my %nrstr function?

sas,sas-macro
My goal is to pass percent symbols into a string. There is no result to the %put when I run: %let foo = %nrstr(bar%x%); %put foo is &foo; If run again, I get errors, the first one is WARNING: The quoted string currently being processed has become more than 262...

SAS ODS escape character macro variable error

sas,sas-macro
The SAS v9.4 documentation lists an automatic macro variable &sysodsescapechar which contains the current ODS escape character, assigned using ods escapechar=. Whenever I try to view the macro variable using a %put statement, I get the following error: ERROR: Open code statement recursion detected. This happens when open code erroneously...

SAS Macro and arrays for tables names

sas,sas-macro
i am new into SAS, i am looking at this code: libname FINAL 'C:\PERM'; %LET PERM_DB = C:\PERM_DB.ACCDB; %LET PASS = 1234; %LET EXTRA1 = _A; %MACRO PERM(tablename); PROC IMPORT OUT= FINAL.&tablename&EXTRA1 DATATABLE= "&tableACCESS&EXTRA1" DBMS=ACCESS REPLACE; DATABASE="&PERM_DB"; DBPWD="&PASS"; SCANMEMO=YES; USEDATE=NO; SCANTIME=YES; RUN; %MEND PERM; %PERM(table1); %PERM(table2); %PERM(table3); %PERM(table4); %PERM(table5); %PERM(table6);...

Calling a macro variable from libname

sas,sas-macro
How do I call a macro variable in the from clause of proc sql if I wish to use it in a libname? Let me show you what I mean: options nofmterr; libname FRST "/ecm/retail/mortgage/nbk6kra/LGD/data/frst_201312bkts"; libname HELN "/ecm/retail/mortgage/nbk6kra/LGD/data/heln_201312bkts"; libname HELC "/ecm/retail/mortgage/nbk6kra/LGD/data/helc_201312bkts"; %let pathLGD = /new/mortgage/2014Q4/LGD; %let prod = FRST; /****************...

How to call a macro variable based on a condition

sas,sas-macro
I have a dataset in SAS with a variable condition, that can take values in "01",...,"20" several variables indexed by i, for instance var01, ..., var20 What i want to do is to create a new variable total which is equal to vark if condition=k. I can do it by...

How to Declare Global Array Variable in SAS?

arrays,sas,global-variables,sas-macro
I'm new to SAS and spinning my wheels. The SAS documentation and other Google searches have not helped me figure this out. How can I declare a global array variable that I can use in various procedures to loop through the contents? Here is what I've tried: %let fileArray =...

why macro is creating Leading space while resolving macro in sas?

sas,sas-macro
I am submitting the following SAS code: proc format; picture mysdt low-high = '%Y%0m%0d%0H%0M' (datatype =datetime); run; DATA _NULL_; call symput("Today", Put(datetime(),mysdt.)); run; %put t_&today; The resulting log shows 2 spaces before the datetime: t_ 201504240150 The problem here is when my macro is resolved it is creating leading space....

macro error: A character operand was found in the %EVAL function or %IF condition

macros,sas,sas-macro
%macro test(k); %if &k le 0 %then %put < 0 ; %else %put > 0; %mend test; %test(-5); %test(3.1); But %test(-3.1); will generate error ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &k le 0 I...