I am missing something. Since Counted is the same for all observations, what is the problem with "select distinct AccountId, Month, Counted" ? ... View more

And another way with proc freq.   proc freq data=have; by Name notsorted; table produit / out=want; weight Number; run; ... View more

Hello,   You'll have to give more details than that because your code works fine :     data Dataset_1; length Country Continent $10; input Country $ Continent $; cards; China Asia Japan Asia Malaysia Asia Germany Europe Australia Oceania ; run; data Dataset_2; length Continent Profit_Margin $10; input Continent $ Profit_Margin $; cards; Asia 0.05 Europe 0.08 America 0.02 ; run; proc sort data=Dataset_1; by Continent; run; proc sort data=Dataset_2; by Continent; run; data want; merge Dataset_1 (IN=A) Dataset_2 (IN=B); by Continent; if A; run;  Can you give the complete log for the data step ?     ... View more

hello,   It helps to provide have and want datasets in the form of data steps so we know exactly the type of data you are dealing with.   If your observations are strings in the form you give in your example, you have first to extract numbers, for instance with scan and input functions, then you can perform the summations :   data have; input x $10.; cards; 2 (40.0) 1 (20.0) 2 (40.0) ; run; data want; set have end=fend; drop sum1 sum2; sum1+input(scan(x,1), best.); sum2+input(scan(x,2, '()'), best.); output; if fend then do; x=cat(sum1,' (',put(sum2,5.1),')'); output; end; run;   ... View more

Hello,   As explained by @ChrisBrooks, when no new record is retrieved by a set statement, SAS stops processing further instructions and exits the data step. You can use instead a merge statement as follows :   data test; format dt date9. count best.; stop; run; %macro update(ds, date); data newdate; dt=&date.; run; data &ds.; merge newdate (in=innew) &ds. (in=date_exists); by dt; if innew then do; if date_exists then count+1; else count=1; end; run; %mend; data _NULL_; call symputx("yesterday", intnx('day',today(),-1)); call symputx("today", today()); run; %put &yesterday. &today.; %update(test, &yesterday.); %update(test, &yesterday.); %update(test, &today.); %update(test, &today.); %update(test, &today.); ... View more

Hello,   Try as follows.   data have; input A B C; cards; 1 2 3 4 5 6 ; run; data labels; input var $ label $; cards; A label_A B label_B C label_C ; run; data _NULL_; set labels end=fend; if _N_=1 then call execute('data want; set have; label '); call execute(cats(var,'="',label,'"')); if fend then call execute('; run;'); run; ... View more

Hello,   Not tested :   proc sql noprint; CREATE TABLE want AS SELECT a.*, count(b.PORT) FROM a LEFT JOIN b ON strip(upcase(b.Name))=strip(upcase(a.NAME)); quit; According to the desired output, you may want to use count(DISTINCT PORT).   Edit : Forgot to group by Name. See @SwissC's answer instead. ... View more

Hello,   Assuming alcoholuse end ciguse can take values 1, 2 and ., here are the possible outcomes. You can check if it fits your needs.   /* Possible values for ...use variables : 1, 2, . */ data usevals; input x; cards; 1 2 . ; run; /* We generate all possible combinations of alcoholuse and ciguse */ proc sql noprint; CREATE TABLE have AS SELECT a.x AS ciguse, b.x AS alcoholuse FROM usevals a, usevals b; quit; data want; set have; if ciguse=1 and alcoholuse=1 then bothsubstances=1; else if ciguse=2 or alcoholuse=2 then bothsubstances=2; else bothsubstances=.; run; ... View more

Hello,   If you read the log, you will see that your program produces an error :   10 ; 11 RUN; 12 DATA _LIC2; 13 SET _LIC; 14 ID_NEQ = QUOTE("'",LIC,"'"); ----- 72 ERROR 72-185: The QUOTE function call has too many arguments. 15 RUN;   Indeed, according to the documentatiopn, the quote function has two arguments :   QUOTE(argument-1, argument-2) Required Arguments argument-1 specifies a character constant, variable, or expression. argument-2 specifies a quoting character, which is a single or double quotation mark. Other characters are ignored and the double quotation mark is used. The double quotation mark is the default.   => Replace your call to quote function with ID_NEQ = QUOTE(LIC,"'"); ... View more

Hello,   Not tested as you did not provide data in a usable form :   proc sql; create table tm.testrun as select distinct source, count(source)as s_sum, encounter_id, count(encounter_id)as e_sum from Tm.med group by source having sum(Medication_code ne " ")>0 and sum(Medication_name ne " ")>0; quit; ... View more