I'm trying to left join table2 to table 1, but it needs to join by the closest match, instead of exact. For example, Table 1 has a column year with values 1990, 2000, 2010, 2020. Table 2 has column year with values 1991, 2002,2015,2030. 1990 should join with 1991, 2000 to 2002, 2010 to 2015 and 2020 to 2030.   Table 1 should match the years in table 2 that is closest and not higher than Table1.   how would I do this? ... View more

I have a variable z=10, but in a character format. To multiply it by 2, i convert the z to a numeric during my multiplication: 2*input(z, 8.2). However, my result is .2, not 20. Why is this the case? It is onlu 20 when I use input(z, 8.) ... View more

suppose I have a query that is has a lot of sums, but I also want to sum these functions in a subsequent column proc sql; select sum(fee1,fee2,fee3) as Fee123, sum(fee4,fee5,fee6) as Fee456, sum(Fee123, Fee456) as TotalSUM quit; I know that TotalSUM will not work and the only way to do this is the query below or I can put this in a table and then sum Fee123 + Fee456     sum(sum(fee1,fee2,fee3), sum(fee4,fee5,fee6)) as TotalSUM   but is there a way to do this cleanly and easily (either procsql or datastep)?  My query has a lot of aggregates of aggregates and I want it to simplify this and also avoid creating a bunch of temp tables to make it work. ... View more

I have a sample proc sql code that wont run. proc sql; select (CASE WHEN t1.variable = 0 THEN 'No' ELSE t1.variable end) as ColumnName FROM TABLE T1; quit; Running this gives me an error of: "Result of WHEN Clause 2 is not the same data type as the preceding results"     Is it because the result wants to be an integer?  If so, how do I out put a "NO"?   I tried to put a colon between the 0, but get the following: Expressing using equals (=) has components of different data types result of when clause 2 is not the same data type as preceding results ... View more

I have a sas output that outputs an integer result as text.  For example, instead of "1" the output is "01".   However when I export or copy and paste it to excel,  my '01' is now just '1'.   how do I make it where my output is '01'?  Is this something I can do in SAS or is this something from excel? ... View more

I asked this question in the "SAS programming forum" and I was given suggestion to use %let, so I think it might be better to ask here, since I'm actually using SAS EG.   I have a proc sql query as follows: proc sql; select * from table where ID in %Bquote('&ID'); quit; I want it to do the equivilant as proc sql; select * from table where ID in ('2','3','5'; quit; I have my prompt set up to accept multiple values but no matter what, it always just filters my results based on the first value ('2').   I want the user to pick ID in the prompts, not in the code itself using the %let     ... View more

I'm have a code as show below: proc sql; select * from table where ID in %bquote('&ID'); quit; and I want it to do the equvilant of: proc sql; select * from table where ID in ('1','2'); quit; however, no matter how hard I try, I only get one ID with the prompt..   what could be the issue? ... View more

I usually like to view a quick 5 to 10 rows of my table so I can actually see the column names, and also get a feel of what sample data looks like.   I always tend to use the following: proc sql inobs=10; select * from table; quit; Is this the best way to view a quick sample of my data? From my experience, sometimes inobs=10 can generate a table really quick, and other times, it takes a long time to run.     Maybe I don't quite understand what an "inobs" does, but is this the best way to get a very fast look at my data? ... View more

Suppose i have 3 tables: My query is essentially Select * From T1 Inner join T2 on T1 Inner join T3 on T2 Where T1.filter = somefilter. I understand T2 will join into the filtered T1 because of the where statement. Does T3 join on the newly filtered T2(because of T1), or will it try to join T2 as if it is unfiltered ... View more