Hello,    I do have excel file which contains number in string format. Column header name is with space and I want to import file in SAS and convert string to numeric by using input function or something similar. SAS variable name should be sas naming format (space remove by _).    test name 5,410.12 565.56 12,345.00   proc import out=test datafile='C:\desktop\test.xlsx' dbms=xlsx replace; getnames=yes; run; data test; set test; test_name = input('test name'n, best12.); run;  I need to create something like following output:      ... View more

Hello,    I am running a SAS macro to sort my dataset. I do have multiple folders and each folder has different number of datasets. Is there a way for us to modify SAS macro which can go to specific folder and bring all sas file name as macro parameter. So I just need to change library name and run the macro instead of changing sas dataset name for macro variable.    For scenario 1, I do have five (5) sas files in folder so I need to run my macro five times.  Whereas, in scenario 2, I do have three (3) sas files in folder - and I do need to run my macro only three times.  Scenario 1:    Scenario 2:               libname abc 'C:\Users\XXX\Desktop\platform\one'; %macro test (var); proc sort data=&var. out=new_sort_&var.; by item; run; %mend; %test(test1); %test(test2); %test(test3); %test(test4); %test(test5);     ... View more

Hi,  I need to find how many times account_id is repeating along with department.  I tried to use first. procedure for this problem. I was able to find how many times account_id is repeating; however, I was not able to get the desire value for department.  Also, do we have proc sql procedure to do something similar in SAS?    data have; input Account_Id Dept Salary Emp_Status $ Projects $ Rating; cards; 111 123 7000 Perm A 5 111 123 6000 Perm B 4 111 123 7000 Perm C 5 222 124 6000 Perm A 5 333 125 7000 Perm B 4 333 125 7000 Perm C 5 ; run; proc sort data=have; by account_id dept; run; data want ; set have; by account_id; if first.account_id then do; flag = 0; if first.dept then flag_1 = 0; end; flag_1 + 1; flag + 1; retain flag_1; retain flag; run;   ... View more

Hello,    I am requesting some help to validate left join (or any other join) result  to verify we do not have any error.  I do need to get this in efficient way. Here is an example:  If I do left join test1 and test2, I need to show that I am not missing any possible observation from test2.  Thank you,    data test1; input var1 $ var2; datalines; test1 3 test2 4 test3 5 test4 6 ; run; data test2; input var1 $ var3; datalines; test1 3 test2 4 test3 5 test5 7 ; run; /*left join*/ data test3; merge test1 (in=a) test2 (in=b); by var1; if a; run; /*get data from right table only*/ data test4; merge test3 (in=a) test2 (in=b); by var1; if b and not a; run; /*validate left join data*/ /*We will get 0 observation*/ data test5; merge test3 (in=a) test4 (in=b); by var1; if b and a; run; ... View more

Hello,    I need to write a count macro which is calculate distinct value by variable and populate the table. For example, in test1 dataset, I do have total 5 observations but out of 5, 4 are unique. If I run the macro, I need to have final table listing as below.    data test1; input var1 var2 $; datalines; 001 001 002 003 004 ; run; data test2; input var1 var2 $; datalines; 001 001 002 002 004 ; run;   ... View more

Hello,    I do need to use proc sql to merge two dataset.  I am getting an error when I am using following code. It seems like I am getting error from line 4 ( from lptssp.dm a ).  SAS Code:   proc Sql; create table _input_dataset_ex as select a.usubjid, a.arm, from lptssp.dm a right join (select * from &input_dataset_ex) b on a.usubjid=b.usubjid where a.arm ne ''; quit; Error: Error log:    MPRINT(D0UNBLIND0EX): proc sql; NOTE: Line generated by the invoked macro "D0UNBLIND0EX". 218 create table _input_dataset_ex as select a.usubjid, a.arm, 218! from lptssp.dm a right join (select * from &input_dataset_ex) b on a.usubjid=b.usubjid ------ - 22 79 218! where a.arm ne ''; quit; */ ; proc sort data=_input_dataset_ex SYMBOLGEN: Macro variable INPUT_DATASET_EX resolves to lptssp.ex MPRINT(D0UNBLIND0EX): create table _input_dataset_ex as select a.usubjid, a.arm, from lptssp.dm a right join (select * from lptssp.ex) b on a.usubjid=b.usubjid where a.arm ne ''; ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. ERROR 79-322: Expecting a FROM.   ... View more

Hello,    I have following step in proc sql.  How can we get this step in one datastep instead of data and sort procedure /*Have*/ proc Sql noprint; create table cars as select * from sashelp.cars where upcase(make)="%upcase(HONDA)" order by length, mpg_city, mpg_highway; /*Replaced element with study_arm_desc variable. DT*/ quit; /*want*/ data cars; set sashelp.cars; if make = "Honda"; *to add sort steps in this step; run; proc sort data=cars; by length, mpg_city, mpg_highway; run;   ... View more

Hello,    I need to create a variable and assigned a character value based on the criteria.  I need to do this step by using the proc sql.    When I am using following code, I am getting an error.  ______ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, ELSE, EQ, EQT, GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, WHEN, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored. proc sql; create table test2 as select *, case when ( length(strip(_datetime)) GE 9 ) then when _date1 GT _date2 then 'N' end as var1_new end from test1 ; ... View more