@Demoxe: Sorry, but your data step is just very difficult to read and probably incorrect. Based on the variable names, I guess what you are trying to do is to calculate total cost of phone calls for each subscriber (abonent). The price of a phone call is calculated by multiplying the callTime and price. The price seems to be determined by the value of id_tarif, which in turn decided by the abonent and the date range within which the call date falls into. If this is a correct guess, then it is quite easy to do. In the below, the total cost for the abonent 10 is 230, because abonent 10's first phone call is made on the date 17103, which is between 17000 and 17199, during which the tarif was 7 thus the price was 5, while the three remaining calls are made during the dates when the tarif was 1 thus the price was 5. Total cost thus was: 1*5 + 9*9 + 8*9 + 8*9 = 230. [pre] /* test data */ data tarifs; input id_tarif price; cards; 1 9 2 8 3 10 4 6 5 3 6 7 7 5 8 4 9 5 10 7 ; run; data abonents; input id_abonent id_tarif date_begin date_end; cards; 1 7 17000 17200 1 2 17201 17400 2 9 17000 17200 2 8 17201 17400 3 3 17000 17200 3 10 17201 17400 4 10 17000 17200 4 9 17201 17400 5 1 17000 17200 5 3 17201 17400 6 8 17000 17200 6 10 17201 17400 7 8 17000 17200 7 2 17201 17400 8 10 17000 17200 8 3 17201 17400 9 10 17000 17200 9 7 17201 17400 10 7 17000 17200 10 1 17201 17400 ; run; data calls; input id_abonent dateCall timeCall; cards; 2 17099 8 2 17111 5 2 17175 9 3 17019 4 3 17130 3 3 17215 6 3 17268 10 4 17119 5 5 17038 1 5 17095 5 6 17064 7 6 17201 6 6 17298 8 7 17014 3 7 17057 3 7 17148 4 7 17199 8 8 17079 9 8 17134 4 8 17206 2 9 17210 10 10 17103 1 10 17217 9 10 17217 8 10 17247 8 ; run; proc sql; /* attach price to abonents matching tarifs */ create view prices as select a.*, t.price from abonents as a left join tarifs as t on a.id_tarif=t.id_tarif; /* calc the cost of each call looking up the tarif (and thus the price) based on the call date and multiplying the price with the call time. assumes that there are no call dates that match multiple date ranges */ create view costs as select c.*, c.timeCall*p.price as cost from calls as c left join prices as p on c.id_abonent=p.id_abonent and p.date_begin <= c.dateCall and c.dateCall < p.date_end; /* calculate total cost for each abonent */ create table totals as select id_abonent , count(*) as num_calls , min(dateCall) as date_from , max(dateCall) as date_to , sum(cost) as total_cost from costs group by id_abonent order by id_abonent; /* check */ select * from totals; quit; /* on lst id_abonent num_calls date_from date_to total_cost ------------------------------------------------------ 2 3 17099 17175 110 3 4 17019 17268 182 4 1 17119 17119 35 5 2 17038 17095 54 6 3 17064 17298 126 7 4 17014 17199 72 8 3 17079 17206 111 9 1 17210 17210 50 10 4 17103 17247 230 */ [/pre] ... View more

If you don't need to maintain much numeric precision, then you can do something like below to take advantage of %sysevalf(). On my moderate PC, the second data step has 1M obs but ran in less than 30 seconds. Hope this helps a bit.    /* test data */    data one;      do i = 1 to 1e6/2;        a=1; b=2; c='a+b'; d=0; output;        a=1; b=2; c='a-b'; d=0; output;      end;    run;      data two;      set one;      length m $200;      retain popen '(' pclose ')';      m = c;      m = transtrn(m, 'a', catt(popen, put(a,best.-l), pclose));      m = transtrn(m, 'b', catt(popen, put(b,best.-l), pclose));      m = catt('%sysevalf', popen, m, pclose);      d = input(resolve(m), best.);      keep a b c d;    run;    /* on log    NOTE: There were 1000000 observations read from the data set WORK.ONE.    NOTE: The data set WORK.TWO has 1000000 observations and 4 variables.    NOTE: DATA statement used (Total process time):          real time           25.53 seconds          cpu time            25.52 seconds    */      proc print data=two(obs=3);    run;    /* on lst     Obs    a    b     c      d       1    1    2    a+b     3       2    1    2    a-b    -1       3    1    2    a+b     3   */ ... View more

... > I saw that this option > in infile statement - DLMSTR has facility to read > either of ways that data will come and is applicable > in SAS 9 version. ... No. DLMSTR= option lets you specify a multi-character delimiter. DLM= option lets you specify single-character delimiter(s). A rather big difference. [pre] data one; infile cards dlmstr="+|+" missover; input (v1-v3) (:$8.); cards; 1+++|+2||++|+3 ; run; proc print data=one; run; /* on lst Obs v1 v2 v3 1 1++ 2||+ 3 */ data two; infile cards dlm="+|+" missover; input (v1-v3) (:$8.); cards; 1+++|+2||++|+3 ; run; proc print data=two; run; /* on lst Obs v1 v2 v3 1 1 2 3 */ [/pre] ... View more

... > Obviously, I need a comma seperated variable list. > Can any one please show me the quickest way to do > this? Thanks a lot for your help!! ... Here is a simple macro that converts input blank separated list into a comma separated list. Hope this helps a bit. [pre] %macro b2c(src); %local comma blank; %let comma = %str(,); %let blank = %str( ); %let src = %sysfunc(strip(&src)); %sysfunc(translate(&src,&comma,&blank)) %mend b2c; %put ***%b2c(x y z)***; %*-- on log ***x,y,z*** --*; [/pre] ... View more

@Scott: Technically speaking, INTCK function is *not* appropriate for calculating the duration between two time points, especially the unit of time you use is larger than the accuracy of the time points you have. For example, I don't think it is appropriate to measure the duration between two datetimes (measured at the second unit) using a function call like INTCK('day',...). The reason being that the INTCK function enumerates the *boundaries* in the given interval. This means that in terms of 'DAY' interval (without any shifts), the INTCK counts how many midnights(00:00am) there are between the two given time points, which may be somewhat different from the counts of how many 24-hours between the two given time points. In this case, OP is clear about what s/he wants -- the duration --, thus I would simple calculate it by subtracting the start from the finish (this will give the duration in terms of seconds). If the OP is asked to report it in other time units(minutes, hours, days), then a simple division/multiplication will do. ... View more

... > I am importing a txt file into SAS and looking only > for rows that contain the four types of text strings > below ("date", "ini", "type", "case"). Four rows at a > time are related to the same case number. The > challenge is that the order of type and case is not > always the same in the text file and so I would like > to sort by case number to obtain the same order in > all by-groups (and use proc transpose afterwards). ... If I understand correctly, then you want to read from a text file with only the rows that have all of these four keyword: value pairs. Only the problem is that the orders of type and case change. No problem. Use absolute column pointer controls. Hope this helps a bit. [pre] data cases; length date ini type $8; input @1 @("date:") date $ @1 @("ini:") ini $ @1 @("case:") case @1 @("type:") type $; /* all four should not be missing */ if not missing(date) & not missing(ini) & not missing(case) & not missing(type) then output; cards; date:xxx ini:hli type:l case:123 date:yyy ini:lbj case:587 type:l date:zzz ini:jcg type:t case:789 ; run; /* check */ proc print data=cases; run; /* on log Obs date ini type case 1 xxx hli l 123 2 yyy lbj l 587 3 zzz jcg t 789 */ [/pre] ... View more

> Hi everyone, > > I am trying to import a series of files with > different names. The macro didn't work and I sort of > know why, but I don't know how to get around this > still using a revised macro (and not avoiding the > macro). The error originates from using the macro > quoting function %SCAN inside double quotes, in which > everything is treated as a character/string of > characters, and as a result, this function is > useless. How can I fix this error? Thank you very > much. > > Here is the code > > %macro import_tfo; > %local filenames filecount; > %let filesnames= address_type > country /*a > /*a lot of filenames in here*/ > ; > %do filecount=1 %to 32 %by 1; > proc import > datafile="C:\TMP\tfo.%scan[/b](&filenames > , &filecount).out" > out=mf.tfo_%scan(&filenames, &filecount) > dbms=dlm replace; > delimiter='|'; > getnames=no; > run; > %end; > %mend import_tfo; ... A better way to write a macro for something like this is to write two macros, instead of one. One for doing a task (here importing a file), and another for looping over a list of items. In this way, you first write the first one and test it out, and then when it works well, you concentrate doing the second step (or not). Hope this helps a bit. [pre] %macro doOne(datafile=); %if %superq(datafile)= %then %return; %*-- do something with datafile --*; %put datafile=&datafile; %mend doOne; %macro doAll(list=); %local el i; %let i = 1; %let el = %scan(&list, &i); %do %while(&el^=); %doOne(datafile=&el) %let i = %eval(&i + 1); %let el = %scan(&list, &i); %end; %mend doAll; %*-- you can test one or two --*; %doOne(datafile=a) %doOne(datafile=b) %*-- on log datafile=a datafile=b --*; %*-- if it works well and do a lot --*; %doAll(list=c d e f g) %*-- on log datafile=c datafile=d datafile=e datafile=f datafile=g --*; [/pre] ... View more

... > To order variables in a dataset you can use a RETAIN > statement listing variables in the order that you > need. This statement should be placed before SET > stetment, like this: > [pre] > data a; > retain A,B,C,D; > set a; > run; > [/pre] ... @SPR: No. Retain does not take a comma delimited list of variables. Hardly any statements do. Use any of the variable lists. For the re-ordering purpose, use retain before set and don't use length statements at all. Hope this helps. ... View more

... > You cannot both create and use a macro variable within > 1 DATA step program. That means if you create > &col_EXP_DT in a DATA step program, that a step > boundary must be encountered before you USE > &col_EXT_DT in a program. > > See this Tech Support note (and the Macro Facility > documentation) for more details: > http://support.sas.com/kb/23/182.html ... I guess the note is not 100% clear if even a long time user ends up drawing a wrong conclusion like that. You definitely *can* both create and use a macro variable within a data step. [pre] %symdel mvar; data _null_; call symput('mvar', 'what'); something = resolve('&mvar'); put something=; run; /* on log something=what */ [/pre] Message was edited by: chang_y_chung@hotmail.com ... View more

Scott wrote: > Exactly how are you doing the "lookup table" > invocation at SAS compilation time - that would be an > interesting point to clarify? I am not OP, so I can only guess. But it is quite easy to imagine a data step validating a lot of variables based on a lookup table with the variable name as the key that stores validating expression(s) for each variable. Below is an over-simplification (you should *not* rely on the global macro variables as the lookup table.), but illustrates the concept. [pre] /* validation macro looking up based on var name */ %macro inRange(var); &&&var.cond %mend inRange; /* prepare lookup table */ data valid; input var $ cond & $30.; call symputx(catt(var,"cond"), cond, "g"); cards; age 13 <= age <= 14 height 63 <= height <=65 ; run; /* validation */ data _null_; set sashelp.class; ageOK = %inRange(age); heightOK = %inRange(height); if ageOK and heightOK then put name= age= height=; run; /* on log Name=Henry Age=14 Height=63.5 Name=Judy Age=14 Height=64.3 */ [/pre] ... View more

> Thanks, I am looking for a condition that FOLLOWS the > variable only as I am doing a lookup of a table that > contains only the part after the variable. i.e. I am > looking for "IF X then ...", where > is kept in the lookup table. E.g. > is , except you > can't use BETWEEN with the IF statement. ... If the can contain the name of the variable (which will likely be the case when the lookup key is indeed the variable name), then you can do something like below. That is the part being: [pre] = ifn(x=0|missing(x), 999, ifn(1.3<=x<=10.7,x,-x)) [/pre] hope this helps a bit. [pre] data _null_; do x = ., 0, 1.26, 6.26, 10.71; if x = ifn(x=0|missing(x), 999, ifn(1.3<=x<=10.7,x,-x)) then y = 1; else y = 0; put x= y=; end; run; /* on log x=. y=0 x=0 y=0 x=1.26 y=0 x=6.26 y=1 x=10.71 y=0 */ [/pre] Wait a minute. If you are *not* allowed to do a like: [pre] >= 1.3 and x <= 10.7 [/pre] then, there is no hope. This cannot be done, as far as I know, that is. ... View more

The reason for the problem boils down to your macro %test generating the following code (as reported on log by turning on the mprint option): [pre] MPRINT(TEST): DATA _NULL_; MPRINT(TEST): DO INC=1 TO 3; MPRINT(TEST): PUT "2"; MPRINT(TEST): END; MPRINT(TEST): RUN; [/pre] If you really want to return 2, 3, 4, then your macro should be something like: [pre] %macro count(from=2, to=4); %local i; %do i = &from %to &to; %if &i > &from %then %*;,; %*;&i %end; %mend count; %put %count(to=4); %*-- on log 2,3,4 --*; [/pre] This may be too advance for you, but just in case, looping over a list of blank delimited words is such a common task that there have been much development in making it simple. For instance, you can separate the looping part out into a separate macro so that you can do something like this one posted on the sas-l: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1011a&L=sas-l&O=D&P=999 ... View more

The reason why data _null_'s solution is working for the test string is nothing to do with multi-character delimiters. This works because Q option ignores any delimiter characters inside quoted string; and because scan, by default, does not return (or count) 'null' words -- meaning consecutive delimiters are considered as one. See my sas-l post for a true multi-character delimiter support via a variant of coding techniques known as "_infile_ magic": http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010d&L=sas-l&D=1&P=26236 Or toby's macro relying on call prxnext with a cleaver use of non-greedy modifier(*?) to the perl reg ex wild card character (.) at: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010d&L=sas-l&D=1&O=D&P=27264 ... View more

See if this one on sas-l helps: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010A&L=sas-l&P=R22861&D=1&H=0&O=D&T=1 ... View more

If you have sas/stat licensed, then the calculation takes only a few lines. Reshaping from the matrix to an edgelist is left as homework. [pre] data one; input id $ x y @@; cards; S1 1 2 S2 1 3 S3 2 3 S4 5 6 ; run; proc distance data=one out=dist shape=square method=euclid; var ratio(x y); id id; run; /* check */ proc print data=dist noobs; format s: f6.4; run; /* on lst id S1 S2 S3 S4 S1 0.0000 1.0000 1.4142 5.6569 S2 1.0000 0.0000 1.0000 5.0000 S3 1.4142 1.0000 0.0000 4.2426 S4 5.6569 5.0000 4.2426 0.0000 */ [/pre] If you don't have the luxury of having sas/stat licensed, then sql takes only a few lines more: [pre] data one; input id $ x y @@; cards; S1 1 2 S2 1 3 S3 2 3 S4 5 6 ; run; proc sql; create table dist as select d1.id as id1, d2.id as id2 , sqrt((d1.x-d2.x)**2 + (d1.y-d2.y)**2) as dist from one as d1, one as d2 where d1.id not = d2.id order by d1.id, d2.id; /* check */ select id1, id2, putn(dist,"6.4") as dist from dist; quit; /* on lst id1 id2 dist -------------------------- S1 S2 1.0000 S1 S3 1.4142 S1 S4 5.6569 S2 S1 1.0000 S2 S3 1.0000 S2 S4 5.0000 S3 S1 1.4142 S3 S2 1.0000 S3 S4 4.2426 S4 S1 5.6569 S4 S2 5.0000 S4 S3 4.2426 */ [/pre] ... View more