I have 2 datasets - grpA and grpb having id and salary. I want to compare and output the largest salary from 2 dataset.     proc compare base = sashelp.grpa compare = sashelp.grpb; var salary; run; By proc compare we can only compare   Let  me know is there any way to output the largest value. ... View more

data customer1; input acctno trnsdt date10. trnsamt ; format trnsdt date9.; cards; 1 15jan2020 300 1 15mar2020 1400 1 16mar2019 1450 1 16mar2019 1200 1 18mar2020 1800 ;run;   The data set executes successfully,   proc sql; select acctno,trnsdt,max(trnsamt) as max from from customer1 group by acctno,trnsdt;   But my proc sql query is not running . I'm not able to figure out the error. Error: ERROR: File WORK.FROM.DATA does not exist. Let me know how can i resolve this.   ... View more

data country; input id name$ country$; cards; 11 alex usa 12 reno uk 14 tanya india 15 john china ;run;   as id columns is unique, i want to create new dataset based on id column.   %macro createm; proc sql noprint; select distinct id into :id1-:id99 from country; quit; %let cnum=&sqlobs; %do i=1 %to &cnum; %let dsname=name_&&id&i; data &dsname; set country; where id =&id; run; %end; %mend;   Options macrogen symbolgen mlogic mprint; %createm;   here datasets are created but datasets are blank and i'm getting the error at where line : ERROR: Syntax error while parsing WHERE clause.   when i change it to where id ="&id" //then here also getting error :Where clause operator requires compatible variables. also can i make it more dymanic here like id1-:id99 ,can i pass a macro here without knowing the no of observations     Pls help     ... View more

Hi im calculating average using data step method but unable to do so.   data : data a; input product$ sales day$9. week; cards; a 100 01-jan-19 1 a 20 01-jan-19 1 b 200 02-jan-19 1 h 100 09-jan-19 2 i 300 09-jan-19 2 ; run;   however i can do it using proc means: proc means data=a nway; var sales; class product; output out=s mean=avg; ;run; Results: product _type_ _freq_ avg a 1 2 60 b 1 1 200 h 1 1 100 i 1 1 300   I want to get this output using data step way: proc sort data=a ; by product week; run; data want; set a; by product week; if first.product then sumsales=0; sumsales+sales; if last.product; avg=mean(sumsales); run;   the mean function is not working and returning the exact values of sumsales column   Plz help!!       ... View more

I have a dataset spend with numeric values -   data spend; input custid week spends; cards; 1 1 4365 1 2 457 1 3 124 1 4 35547 2 5 4678 2 6 21253 3 7 32534 3 1 23423 3 2 23567 ; run;   i want output to look like   1 1 4365 1 2 457 1 3 124 1 4 35547 1 5  . 1 6  . 1 7  . 2 1  . 2 2 . 2 3 . 2 4. 2 5 4678 2 6 21253 2 7 . 3 1 23423 3 2 23567 3 3 . 3 4 . 3 5 . 3 6 3 7 32534 So basically i want to increment the values of week to 7 and those which don't have any values in spend should be missing.   data a; set spend; do j = 1 to 7; week = j; output; end; drop j; run;   in above code cust id 1 and 3 are getting repeated 2 times and spend values are also not correct.   Please help.         ... View more

  data test; input ssn $9. ee_id$ EE_pyin $10. payamt; cards; 093443789 322189 2018-06-01 1200 093443789 322189 2018-06-15 1200 093443789 322189 2018-06-29 1200 093443789 322189 2018-07-01 1300 094646665 112782 2018-03-09 2807 094646665 112782 2018-09-21 0054 ;run;   in the above program there are 2 different ssn(social secuirty number), i want to count how many times he has paid.Till here it is working fine .But i dont want to include the last payment or you can say exclude the latest date for every ssn and then output all others as you can see below.   Pls help!!   proc sort data=test;by ssn;run;   data a c1 c2 c3; set test; by ssn; if first.ssn then c=0; c+1; if c=1 then output c1; if c=2 then output c2; if c=3 then output c3; run;   ... View more

i want to find 2nd highest salary of each employee.   data sal; input id name$ salary dt date11.; format dt date9.; cards; 101 nick 45000 01jan2019 101 nick 50000 01feb2019 101 nick 52000 01mar2019 101 nick 53000 01apr2019 102 mark 55000 01jan2019 102 mark 56000 01feb2019 102 mark 57000 01mar2019 102 maek 58000 01apr2019 ;run;   i have done using sql   proc sql; select max(T.salary) as sal,T.id from sal T inner join (select max(salary) as sal, id from sal group by id) TT on T.salary<>TT.sal and T.id=TT.id group by T.id; run;   Is there any more simple method in SQL .    ... View more

DATA A; X=1230; Y=PUT(X,Z6.); Y1=INT(LOG10(X)+1); Y2=SUBSTR(LEFT(10**(6-Y1)),2); Y3=CATS(Y2,X); RUN;   output is:x-1230 y-001230 y1-4 y2-00 y3-001230   so i wanted to know how this program is working  in line Y2=SUBSTR(LEFT(10**(6-Y1)),2);,what is the use of "10**",how this is working and what impact it has on the numbers. and how it got to know that it had to substract 4 from y1 without refrencing y .     ... View more

im trying to find out column level mean function.   data m; input name$ var1 var2 var3; cards; a 10 0 5 b 1 2 0 c 9 5 8 ;run;   proc means data=m nway noprint; var var1 var2; output out=rmean; run;   proc sql; create table m3 as select mean(var2) as mean from m; quit;   now proc means and proc sql both are giving column wise mean value but i was trying to use this but why this is not working in data set approach:---   data m2; set m; c1=mean(var2); run;   this is not working plz help me ... View more