I tried creating all the values in macro variable for 600 variable names and 600 values and tried running the do loop: DATA LIB.SUB_FINAL1; SET LIB.SUB_FINAL;   %do i=1 %to 600 %by 1;   %IF &&VARNAME&I=. %THEN &&VARNAME&I=&&PERC_MEDN&I; %end; RUN; Thought values are resolving correctly, but its not doing, what it is intended to do. e.g. log varname344 resolved to amount_ex %IF condition &&VARNAME&I=. is FALSE now amount_ex is present in the dataset, but why it is saying false? does it run only over one row? do i need to use some other counter like j=1 to 80000 to run over all rows? ... View more

NO, they don't have IML. ... View more

Thanks Xia, your inputs have always been great. But IML comes as separate package for SAS and we are using enterprise guide. ... View more

the problem is all my variables have customized names. and for merging based on variable names, i believe require transpose? ... View more

Thanks for your help. Since there can be duplicates for the geo_key. Its giving me multiple columns around col1-col50. So just summing across col1 wont solve my problem. So i summed across all rows and set a new indicator to 1 if value is >=1. and then summed on that variables, which seems to be doing the job. ... View more

The problem is that geo_key might be repeated(so I am having multiple columns), so i want to count the distinct value of it, when indicator is 1 for that variable. So scenario will change for each variable. So basically I want the count to match to below query: select count(distinct geo_key) from table where var1=1; select count(distinct geo_key) from table where var2=1; ..... select count(distinct geo_key) from table where var50=1; ... View more

This gives the count by geo_key. I need count of geo_key by variables var1-var50. ... View more

I got your point. But if it happens to be large data, it would be waste of space and even take lot time, while i need only specific matching rows. ... View more

I am not sure how view is defined, but %tslit macro function stated above, seems to be doing the job for me. ... View more

OPS_SCOPE is a view. So, i think thats why its SQL server syntax. ... View more

(You mean this statement creating problem: proc sql; connect to odbc as mycon    (datasrc=cdom3_ops); create table scope_table as select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%"); quit; I am not much familiar with database interfacing, but this is what we usually do to connect to SQL server. ... View more

proc sql; SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%"; quit; ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name        'Lialda%'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. The above statement works fine with single quotes. ... View more

Thanks, the semi-colon was missing by mistake here. Seems like %tslit is doing the job as expected. But not sure why double quotes throws the error: Invalid column name, even if i directly pull the data, using double quotes. I am using SQL server 2008, and query is in SAS. ... View more