About LaurieF

LaurieF · ‎02-08-2017

This is another difficult problem to solve accurately without having the source data available to test it. However this code wll work. Whether it's what you want is another thing: proc sql; create table dataout.recat1 as select ifn(age in(1, 2, 3, 4, 5), 1, .) as age, ifn(marital in(2, 4), 2, .) as marital, ifn(income in(1, 2, 3), 1, .) as income from dataout.test; quit; The first ifn function tests age to see if it's 1 - 5; if it is, age is reset to 1, but if not it's set to missing. The same pattern is in the next two lines. I use ifn or ifc if there are only going to be two possibilities; if there are more I use case/when/else. If you want other variables in the output dataset, you need to specify them. Note that you would only use update if you were replacing the dataset; in your datastep code you specify a different dataset.

LaurieF · ‎02-08-2017

It's impossible to give a hard-and-fast answer to this, because there are so many environmental things to consider. But try this for a start: LIBNAME lib_OUT "/saswork/mx/temp/"; %MACRO Export_Loop; %DO var_Replicate = 1 %TO 4; %DO var_BuffCnt = 0 %TO 15; %IF &var_BuffCnt = 0 %THEN %LET var_ReadBuff = ; %ELSE %LET var_ReadBuff = %BQUOTE(READBUFF=)%EVAL(500 * &var_BuffCnt); %DO i = 1 %TO 7; %let var_Testfile = OracTST.file_&i; LIBNAME OracTST ORACLE USER=&UserNm PASS=&PassWd PATH='SASDB02' CONNECTION=GLOBAL SCHEMA=&UserNm &var_ReadBuff; PROC SQL; CREATE TABLE lib_OUT.Temp_Out(compress=binary) AS SELECT * FROM &var_TestFile.(ORHINTS='/*+ PARALLEL(16) */'); %PUT %SYSFUNC(COMPBL("Exporting Test File - &var_TestFile" --- "&var_ReadBuff")); ;QUIT; %dataset_delete(lib_OUT.Temp_Out); LIBNAME OracTST CLEAR; %END; %END; %END; %MEND; %Export_Loop; Note I've simplified the assignment of var_testfile - not important, but easier to read. The difference is in placing compression on the SAS dataset. Although all of the data will be brought over the pass-through, the writes into the SAS dataset will be much reduced. I use binary instead of char because it seems to be more efficient on very wide tables. SAS's compression isn't the best, but it will help a little. What would really help would be, were it possible, reducing the columns, especially very wide varchar types, to the bare minumum of what you need.

LaurieF · ‎02-07-2017

Well, you don't need to - my code picks the number of observations up from the metadata for the dataset. It interrogates the number of observations and always gets it right (as long as the dataset is a SAS one), saving you the bother of putting it in as a parameter.

LaurieF · ‎02-07-2017

Here's a simple way of making it work, using the numeric attribute nlobs (number of logical observations (to ignore deleted observations - usually a good idea)) from the dataset: option mprint notes; data condition; input condition_ID cond1 $ value1 ; datalines; 1 a 4 2 c 1 3 a 1 4 f 40 ; run; %macro cond; %local dsid nobs rc; %let dsid = %sysfunc(open(condition)); %let nobs = %sysfunc(attrn(&dsid, nlobs)); %let rc = %sysfunc(close(&dsid)); %do i=1 %to &nobs; data single_condition_&i; set condition(firstobs=&i obs=&i); run; %end; %mend cond; %cond; Note that I've used firstobs and obs instead of _n_ - if condition is large, this will be more efficient. I'm interested - what's the problem you're trying to solve?

LaurieF · ‎02-06-2017

The clue was SQL statement: execute InsightGetGoogleAnalytcisInfo "23JAN2017","20851" 20851 is clearly a SAS date value, but the first parameter is a date I'd expect ODBC (or whatever the engine is) to accept.

LaurieF · ‎02-06-2017

Sorry - my correction, or something else?

LaurieF · ‎02-06-2017

I didn't know you could put a where clause in proc import. Cool! So the date value needs to be converted back into an external representation again. Sorry about that. %let date = 15Oct1983; %let numberofdays = 15; %let date_end = %sysfunc(putn(%sysevalf("&date"d + &numberofdays), date9.)); %put &date_end;

LaurieF · ‎02-06-2017

When you put a variable, rather than a character string, and it doesn't have an specific format, it always suffixes it with a space. The way I always do it is to use the pointer code +(-1). Put that after the value &&var&i, and things should be different, if not spot on. Also you can check the value of &&var&i with the missing function. Like: if not missing(&&var&i) then do; put…

LaurieF · ‎02-06-2017

For a start, you'll have to convert the date into "number of days since 1 Jan 1960". There are a myriad of ways of doing this. What I'd recommend woud be to do it like this: %let date = 15Oct1983; %let numberofdays = 15; %let date_end = %sysevalf("&date"d + &numberofdays); %put %sysfunc(putn(&date_end, date9.)); The macro function %sysevalf, although it is primarily used for floating point evaluations, has a nice side-effect of allowing interpretation of external date values.

LaurieF · ‎02-06-2017

Can you try running this: proc format lib=work; value binfmt(fuzz=1E-12 min=1 max=40) LOW -< 0.55 = "50.0% to 54.9%" 0.55 -< 0.6 = "55.0% to 59.9%" 0.6 -< 0.65 = "60.0% to 64.9%" 0.65 -< 0.7 = "65.0% to 69.9%" 0.7 -< 0.75 = "70.0% to 74.9%" 0.75 -< HIGH = "75.0% to 79.9%" ; run; I took the format that I created it and ran it through my reverse-engineering macro %genfmt. It takes an existing format and creates the code that could have been used to create it. If this doesn't work in your environment, there may be some weird option setting which is overriding your requirements.

LaurieF · ‎02-06-2017

I've just run this code and it worked fine. Is there something else in your original code you may have overlooked? data cntl; infile cards firstobs=2 dsd dlm=' '; attrib eexcl type length=$ 1; attrib fuzz informat=best. format=best.; attrib fmtname length=$ 8; attrib start end length=8 format=best.; attrib label length=$ 30; attrib hlo length=$ 1; input eexcl fuzz fmtname type start end label hlo; cards; EEXCL FUZZ fmtname type start end label hlo Y 1E-12 binfmt N 0.50 0.55 "50.0% to 54.9%" L Y 1E-12 binfmt N 0.55 0.60 "55.0% to 59.9%" Y 1E-12 binfmt N 0.60 0.65 "60.0% to 64.9%" Y 1E-12 binfmt N 0.65 0.70 "65.0% to 69.9%" Y 1E-12 binfmt N 0.70 0.75 "70.0% to 74.9%" Y 1E-12 binfmt N 0.75 0.80 "75.0% to 79.9%" H ; run; proc format cntlin=cntl; run; proc format fmtlib; select binfmt; run;

LaurieF · ‎02-06-2017

Can you attach your test.csv file? It'd be much easier to track things down with that.

LaurieF · ‎02-06-2017

Have you tried it with fuzz=0? (I always use that - any other is problematic)

LaurieF · ‎02-05-2017

I'll leave hints on how to use surveyselect to people who know what they're talking about. I'm the last one to ask! But if you do have to go down the sorting route with ranuni, you can do it in a single pass: proc sql; create table want as select * from have order by site, ranuni(0); quit; You will get: NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. But that's the only downside. I've used this method over millions of rows and it's still very efficient.

LaurieF · ‎02-03-2017

In which case, to make sure it always works, in all instances, convert it into a flat file! So many ways to skin a cat: data all_products; infile cards; length all_products $ 80; input all_products $char80.; all_products = strip(compbl(all_products)); cards; orange apple juice and cola coffee candy and paper banana sugar and pen and book ; run; filename prodfile temp; data _null_; set all_products; file prodfile; put all_products; run; data products; infile prodfile dsd dlm=' '; length product $ 30; input product @@; if product ne 'and' and not missing(product); keep product; run; filename prodfile clear;

Online Status	Offline
Date Last Visited	‎09-02-2025 04:43 PM