If it's about passing a single value as a constant to an SQL statement then consider something like the following (more or less the approach you drafted): data _null_; set yourtable; if then do; call symput('MyMacroVar',cats("'",myvar,"'"); /* pass value of myvar to macro variable &MyMacroVar */ stop; /* no further iterations through dataset */ end; run; proc sql; .... where MyField=&MyMacrovar .... .... quit; If it's about joining (subselect,...) two tables, one in Oracle and one in SAS then let us know. HTH Patrick P.S. Just in case you don't know this one already: options sastrace=',,,d' sastraceloc=saslog; http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a000433982.htm Message was edited by: Patrick ... View more

That should do the job: proc sort data=yourdata; by id date; run; data want; set yourdata; by id date; if last.id then output; run; ... View more

Hi Jun Also the problem is in general not too difficult to solve I can think of a few challenges which might occur on how your raw data look like. Is the record structure really the way you show it to us (several 'observations' in one line)? Could it be that the name is missing (quite possible if there are millions of records) and that therefore you could have 2 to 4 consecutive values in brackets belonging to 2 different 'observations'? Please let me know as the concrete solution will depend on how the data looks like. The easiest way I can think about right now is to use Regular Expressions (funcions PRX.. in SAS) to decide which substring makes up an 'observation' - but Regular Expressions need also some practice to use and understand. Cheers, Patrick ... View more

Hi SAS_Learner The statement else if group=2 or 3 then new-grp='MEDIUM'; always resolves to TRUE. You could write: else if group in(2,3) then new-grp='MEDIUM'; Another possibility would be to create a format (code not tested): proc format; value grpname 1 ='UNKNOWN' 2,3='LOWER' >3 ='HIGHEST' other='UNKNOWN' ; run; data 'C:\MySASLib\Old'; infile ' C:\MYSAS\groups.dat'; input Name $ 1-5 group 6; run; data New; set 'C:\MySASLib\Old'; new_grp =put(group,grpname.); run; proc print data= New; run; HTH Patrick ... View more

Hi Tim Your sample data don't match too well (i.e. different years and area codes in the two datasets). I changed them a bit hoping that my assumptions were right. Have a look at the following code. It might give you some hints even if it might not be exactly what you're looking for. The code prepares mainly one big denormalised dataset (kind of a "datamart"). It shouldn't be too difficult to get what you want by using proc means on this dataset - and there you can also decide how to handle missings. I converted the 'year' and 'day' variables to a variable 'date' containing a SAS date (count of days since 1 January 1960). This way your dates are "unique" for the same day in different years. HTH Patrick data nesting; infile datalines truncover; input ID AREA YEAR $4. START FINISH; datalines; 541 3 2006 172 211 542 3 2006 . . 543 3 2006 145 182 544 3 2006 121 167 545 3 2006 153 187 546 3 2006 171 204 547 3 2006 132 173 548 3 2006 . . 549 3 2006 129 167 550 3 2006 192 225 551 3 2006 110 . 552 3 2006 150 187 553 3 2006 . . 554 3 2006 168 . 555 3 2006 193 227 ; data weather; infile datalines truncover; input AREA DAY YEAR $4. H_TEMP L_TEMP M_TEMP PRECIP; format date ddmmyy10.; date=input(year||'0101',yymmdd8.)+DAY; datalines; 3 104 2006 78 40 59 0 3 105 2006 84 60 72 0 3 106 2006 82 33 57.5 0 3 107 2006 38 32 35 0.06 3 108 2006 42 35 38.5 0.31 3 109 2006 71 40 55.5 0.01 3 110 2006 73 58 65.5 0.24 3 111 2006 71 46 58.5 0.06 3 112 2006 48 33 40.5 0.03 3 113 2006 57 28 42.5 0 3 114 2006 64 33 48.5 0 3 115 2006 65 39 52 0 3 116 2006 60 31 45.5 0 3 117 2006 74 36 55 0 3 118 2006 78 55 66.5 0 3 119 2006 78 37 57.5 0 3 120 2006 64 46 55 0.13 3 121 2006 52 45 48.5 1.11 3 122 2006 51 42 46.5 0.02 3 123 2006 56 32 44 0 3 124 2006 64 31 47.5 0 3 125 2006 64 49 56.5 0.58 3 126 2006 63 49 56 0 3 127 2006 62 48 55 0.08 3 128 2006 63 46 54.5 0.01 ; data nesting2; set nesting; format date ddmmyy10.; if start ne . and finish ne . then do; do ThisDay=start to finish; date=input(year||'0101',yymmdd8.)+ThisDay; output; end; end; else output; run; proc sql; create table NestingAndWeather as select * from nesting2 l left join weather r on l.date=r.date and l.area=r.area order by id,area,date ; quit; Message was edited by: Patrick ... View more

Hi NickSAS Find below the code how SAS provides it to create a random sample without replacement. If you analyse the small differences to your code then you see why your code doesn't really create a true random sample. Cheers, Patrick data work.rsubset(drop=obsleft sampsize); sampsize=10; obsleft=totobs; do while(sampsize>0); pickit+1; if ranuni(0) set sasuser.revenue point=pickit nobs=totobs; output; sampsize=sampsize-1; end; obsleft=obsleft-1; end; stop; run; ... View more

Is the result a text file or a SAS dataset? For a text file: Have a look at the meaning of @ and @@ for the input and put statement. For a dataset: Have a look at 'Proc Transpose' or at the OUTPUT statement. To denormalise a table is often not a good idea - avoid it if you don't have very good reasons for it. Cheers, Patrick ... View more

Hi Inp If I understand that right then you want to have SAS to solve an issue which you have with your data. It sounds to me as if you will need some data cleansing after reading the data. You could use translate for that - translating a defined set of special characters to blanks. More general would be to use Perl Regular Expressions (have a look at PRXCHANGE), "\s" matches all whitespace characters - that is what most probably then shows up as a 'box'. So just translate all whitespace characters to blanks. Something close to the following should do the job: data ... PID=PRXPARSE ('s/\s/ /io'); infile .... truncover; input @; call PRXCHANGE(PID,-1,_infile_); input @1 var1 @3var2; ... Your file and all the processing happens on the Mainframe (EBCDIC) but you see the result in an 'ASCII environment (EG?). It could be (less likely) that there is some problem with the translation (trantab) between this two environments. To see the special character in your Mainframe file use your Mainframe editor and type on the command line "Hex on"; a tab (the most likely problem char) would have the Hex value 05. HTH Patrick P.S: forget about 'pad', just use 'truncover' Message was edited by: Patrick ... View more

Hi Thomas You're very right that there is no forum for DI. And it's even worse because with DI problems one would often have to add a screen shot. As for your question: It sounds a bit strange to me that your job doesn't pick up the data. Because there the metadata have no more influence (they are used to generate the SAS code which is executed). Did you have a look at the generated SAS code? May be something with the mapping is not o.k. Did you propagate/remap your objects? It must be something like that. Have a look at your code - and the log to see where you "loose" the data. HTH Patrick ... View more

Hi zouclay To have it precise I assume you would have to use SAS/Graph with the Annotate facility and Proc Ganno. With the Annotate facility you can define the coordinate system you need and place you letters as precise as you want. The output is a picture. But: Unless you're familiar with SAS/Graph and Annotate it will cost you quite a bit of work to reach your goal. Another way could be to forget about being that precise and recreating the past: You could just print your output using a font like courier with an appropriate size. This would only look close to how it had been - but much easier to implement. You can always use a datastep with put statements to have your output in the columns and lines you want to. If your challenge is about printing labels and the like then have a look a Proc Forms: http://support.sas.com/documentation/onlinedoc/base/91/forms.pdf HTH Patrick ... View more

HTH Patrick data _null_; RId = prxparse('s/(.)\1+/$1/io'); text = 'YYYMMDD__YDDDM'; call prxchange(RId, -1, text); put text; run; ... View more

Or just use dcreate: NewDirectory=dcreate(DirectoryName,'/local/u/abcdef/'); http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a002332693.htm ... View more

What you want is a cartesian product. That's easiest done with SQL: Data ONE; infile cards; attrib COUNTRY length=$20 label='Country'; input country; cards; Belgium France Germany ; run; Data TWO; infile cards; attrib STOCK length=$20 label='Stock'; input STOCK; cards; Stock1 Stock2 ; run; Data THREE; infile cards; attrib PRODUCT length=$20 label='Product'; input PRODUCT; cards; Product1 Product2 ; Run; proc sql; select * from THREE,(select * from one ,two) ; quit; ... View more

Hi Well: Even at SAS Tech Support work only humans... I hope you didn't accept the answer and let them close the track! As I understand it you're not talking about closing SAS EG clients on client machines but about closing inactive sessions on the Workspace Server. The following paper gives a good overview of the architecture: http://support.sas.com/rnd/papers/sugi30/ServerSoup30_040505.pdf As much as I know there is no setting "close if idle for ... sec" What would be a solution is a process on OS level (on the machine where the Workspace server runs) looking for inactive SAS processes and then kill them - you should also be able to decide whether it is a session spawned by the SAS object spawner (SAS EG, DI and other clients) or started by some other process (i.e. SAS batch). Depending on how you use the server rebooting once a night (let's say at 4 in the morning) could be an other approach which is easy to implement. HTH Patrick ... View more