Background: Recently, I got a task to read the text data to SAS automatically. And the text data is transferred via FTP from other application. The users select the data they want on the application, then click the "send" button, and the data will be send to the FTP. So, I write a SAS program to scan the FTP and find the text data, then download it to the local machine. Because the data is selected by different user for different purpose, the text data format is not same. So, I can only use the IMPORT procedure to read the data to SAS data set.     Problem: 1. Some ID variables are composed of more than 15 numbers, for example: 986509856125436590. The IMPORT procedure recognizes it as a numeric variable cause its value is all digits. And yet the numeric variable can not store a such big number, it will be distorted. 2. Some code values are fixed length, and composed of digits. for example: 001, 018, 023. The IMPORT procedure also recognizes it as a numeric variable, which is not we expected.   3. The IMPORT procedure doesn't support double characters delimiter, it will generate extra variables.         Solution in a general way: 1. For problem 1 and 2, I can ask the application guy to add quotation marks to the character values. 2. For problem 3, just use another data step to drop the extra variables.     My Solution: As we know, when the IMPORT procedure reads the delimited file, actually the data is read by data step. The job of  IMPORT procedure is to generate the data step code. So, Why not generate it by myself?    So, I write a macro to do this. Here is the result:   the text data:   Result of IMPORT procedure:   Result of import macro:     proc import datafile='D:\test.txt' out=test_import dbms=dlm replace; delimiter='||'; getnames=yes; run; %custom_import_data(%str(D:\test.txt),%str(||),test_my_import,get_names=yes);     And Here is the macro code, it does solve my problem for now, I'm not sure it can import any kind of delimited file correctly. Maybe there is a bug I didn't notice, and I'm sure there is a way to improve the performance. So, welcome to update it.   %macro custom_import_data(file_path, /*path of delimited file.*/ file_dlm, /*delimiter, can be more than two characters.*/ out_dsn, /*name of output SAS data set.*/ file_encoding=wlatin1, /*encoding of delimitered file.*/ get_names=no, /*whether generates variable names from the first row. */ last_col_dlm=no, /*whether the line is end with the delimiter.*/ guessing_rows=1000); /*the number of rows to scan.*/ %local i; filename read "&file_path" encoding="&file_encoding"; data _null_; infile read obs=1; input; %if %lowcase(&last_col_dlm)=no %then var_num=count(_infile_,"&file_dlm")+1; %else var_num=count(_infile_,"&file_dlm"); ; call symput('var_num',compress(var_num)); %if %lowcase(&get_names)=yes %then %do; do i=1 to var_num; call symput(cats('var',i),strip(scan(_infile_,i,"&file_dlm"))); end; %end; run; %put The number of variables: &var_num; data work._null_; infile read _infile_=line %if %lowcase(&get_names)=no %then firstobs=1; %else firstobs=2; end=last obs=&guessing_rows; array var_type {&var_num} $ 1; array var_length {&var_num}; array var_informat {&var_num} $ 20; array var_format {&var_num} $ 20; length value $ 1000; retain var_: p_date1 p_date2 p_date3 p_time p_datetime1 p_datetime2 p_num1 p_num2; if _n_=1 then do; p_date1=prxparse('/^\d{4}(\\|-|\/)\d{1,2}(\\|-|\/)\d{1,2}$/'); p_date2=prxparse('/^\d{1,2}(\\|-|\/)\d{1,2}(\\|-|\/)\d{4}$/'); p_date3=prxparse('/^\d{1,2}\D{3}\d{4}$/'); p_time=prxparse('/^\d{1,2}:\d{1,2}:\d{1,2}$/'); p_datetime1=prxparse('/^\d{4}(\\|-|\/)\d{1,2}(\\|-|\/)\d{1,2}(:|\s)*\d{1,2}:\d{1,2}:\d{1,2}$/'); p_datetime2=prxparse('/^\d{1,2}\S{3}\d{4}(:|\s)*\d{1,2}:\d{1,2}:\d{1,2}$/'); p_num1=prxparse('/^-?[1-9]\d{0,10}(\.\d+)?$/'); p_num2=prxparse('/^-?0(\.\d+)?$/'); end; input; do i=1 to &var_num; value=scan(line,i,"&file_dlm"); if var_type{i}='$' then do; len=length(strip(value)); if var_type{i}='n' then do; var_type{i}='$'; var_length{i}=len; var_informat{i}=cats('$',len,'.'); var_format{i}=cats('$',len,'.'); end; else do; var_type{i}='$'; if len>var_length{i} then do; var_length{i}=len; var_informat{i}=cats('$',len,'.'); var_format{i}=cats('$',len,'.'); end; end; end; else do; if prxmatch(p_date1,strip(value))>0 then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='yymmdd10.'; var_format{i}='yymmdd10.'; end; else if prxmatch(p_date2,strip(value))>0 then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='mmddyy10.'; var_format{i}='mmddyy10.'; end; else if prxmatch(p_date3,strip(value))>0 then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='date9.'; var_format{i}='date9.'; end; else if prxmatch(p_time,strip(value))>0 then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='time9.'; var_format{i}='time9.'; end; else if prxmatch(p_datetime1,strip(value))>0 or prxmatch(p_datetime2,strip(value))>0 then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='anydtdtm40.'; var_format{i}='datetime19.'; end; else if prxmatch(p_num1,strip(value))>0 or prxmatch(p_num2,strip(value))>0 or strip(value)='' then do; var_type{i}='n'; var_length{i}=8; var_informat{i}='best12.'; var_format{i}='best12.'; end; else do; len=length(strip(value)); if var_type{i}='n' then do; var_type{i}='$'; var_length{i}=len; var_informat{i}=cats('$',len,'.'); var_format{i}=cats('$',len,'.'); end; else do; var_type{i}='$'; if len>var_length{i} then do; var_length{i}=len; var_informat{i}=cats('$',len,'.'); var_format{i}=cats('$',len,'.'); end; end; end; end; end; if last or _n_=&guessing_rows then do; output; do i=1 to &var_num; call symput(cats('var_type',i),strip(var_type{i})); call symput(cats('var_length',i),strip(var_length{i})); call symput(cats('var_informat',i),strip(var_informat{i})); call symput(cats('var_format',i),strip(var_format{i})); end; end; keep var_:; run; data &out_dsn; infile read dlmstr="&file_dlm" dsd %if %lowcase(&get_names)=no %then firstobs=1; %else firstobs=2; ; length %do i=1 %to &var_num; %if &&var_type&i=n %then %let var_type&i=; %if %lowcase(&get_names)=yes %then "&&var&i"n &&var_type&i &&var_length&i; %else var&i &&var_type&i &&var_length&i; %end; ; format %do i=1 %to &var_num; %if %lowcase(&get_names)=yes %then "&&var&i"n &&var_format&i; %else var&i &&var_format&i; %end; ; input %do i=1 %to &var_num; %if %lowcase(&get_names)=yes %then "&&var&i"n : &&var_informat&i; %else var&i : &&var_informat&i; %end; ; run; %mend custom_import_data; ... View more

Hi, guys   About this statement "Subtree selection using validation data can be eliminated by choosing Method=Largest or Method=N in the Method property“,  I can't understand why it is false.    ... View more

Hi, Guys   I'm very confused about the value MIN and MAX of SORTSIZE system option. Here is from SAS Help and Document.   Syntax -SORTSIZE n | nK | nM | nG | hexX | MIN | MAX SORTSIZE= n | nK | nM | nG | hexX | MIN | MAX Required Arguments n | nK | nM | nG specifies the amount of memory in multiples of 1; 1,024 (kilobytes); 1,048,576 (megabytes); and 1,073,741,824 (gigabytes) respectively. You can specify decimal values for the number of kilobytes, megabytes, or gigabytes. For example, a value of 8 specifies 8 bytes, a value of .782k specifies 801 bytes, and a value of 3m specifies 3,145,728 bytes.   hexX specifies the amount of memory as a hexadecimal value. You must specify the value beginning with a number (0–9), followed by an X. For example, the value 2dx sets the amount of memory to 45 bytes.   MIN specifies the minimum amount of memory available.   MAX specifies the maximum amount of memory available.   I've ran some test. If I set SORTSIZE=MIN, the actual value is 0. So I ran a SORT procedure with a large data, too see whether the SAS process would use memory to sort data. And it actually used memory, it's just like when you set SORTSIZE=MAX.   So, I became very confused. What's the difference between MIN and MAX?   /* sample data*/ data test; length id $ 500; set sashelp.cars; do i=1 to 5000; id=put(uniform(i)*1000+_n_,z6.); output; end; run; option fullstimer; /* Set SORTSIZE=MIN*/ option sortsize=MIN; /* Get the value of SORTSIZE*/ %put SORTSIZE is: %sysfunc(getoption(sortsize)); /* Sort the data*/ proc sort data=test out=test1; by id; run; /* Set SORTSIZE=MAX*/ option sortsize=MAX; /* Get the value of SORTSIZE*/ %put SORTSIZE is: %sysfunc(getoption(sortsize)); /* Sort the data*/ proc sort data=test out=test2; by id; run; /*Set SORTSIZE=300M*/ option sortsize=300M; /* Get the value of SORTSIZE*/ %put SORTSIZE is: %sysfunc(getoption(sortsize)); /* Sort the data*/ proc sort data=test out=test3; by id; run; Here is the information of Log, the memory used when SORTSIZE=MIN is just same as when SORTSIZE=MAX. 43 44 45 %put %sysfunc(getoption(memsize)); 2147483648 46 47 /* sample data*/ 48 data test; 49 length id $ 500; 50 set sashelp.cars; 51 do i=1 to 5000; 52 id=put(uniform(i)*1000+_n_,z6.); 53 output; 54 end; 55 run; NOTE: There were 428 observations read from the data set SASHELP.CARS. NOTE: The data set WORK.TEST has 2140000 observations and 17 variables. NOTE: DATA statement used (Total process time): real time 3.18 seconds user cpu time 1.09 seconds system cpu time 0.45 seconds memory 511.43k OS Memory 10468.00k Timestamp 06/28/2017 10:43:37 PM Step Count 5 Switch Count 0 56 57 option fullstimer; 58 59 /* Set SORTSIZE=MIN*/ 60 option sortsize=MIN; 61 /* Get the value of SORTSIZE*/ 62 %put SORTSIZE is: %sysfunc(getoption(sortsize)); SORTSIZE is: 0 63 /* Sort the data*/ 64 proc sort data=test out=test1; 65 by id; 66 run; NOTE: There were 2140000 observations read from the data set WORK.TEST. NOTE: The data set WORK.TEST1 has 2140000 observations and 17 variables. NOTE: PROCEDURE SORT used (Total process time): real time 18.01 seconds user cpu time 5.36 seconds system cpu time 4.68 seconds memory 1686892.54k OS Memory 1696752.00k Timestamp 06/28/2017 10:43:55 PM Step Count 6 Switch Count 0 67 68 /* Set SORTSIZE=MAX*/ 69 option sortsize=MAX; 70 /* Get the value of SORTSIZE*/ 71 %put SORTSIZE is: %sysfunc(getoption(sortsize)); SORTSIZE is: MAX 72 /* Sort the data*/ 73 proc sort data=test out=test2; 74 by id; 75 run; NOTE: There were 2140000 observations read from the data set WORK.TEST. NOTE: The data set WORK.TEST2 has 2140000 observations and 17 variables. NOTE: PROCEDURE SORT used (Total process time): real time 12.06 seconds user cpu time 4.82 seconds system cpu time 3.51 seconds memory 1686892.54k OS Memory 1696752.00k Timestamp 06/28/2017 10:44:07 PM Step Count 7 Switch Count 0 76 77 /*Set SORTSIZE=300M*/ 78 option sortsize=300M; 79 /* Get the value of SORTSIZE*/ 80 %put SORTSIZE is: %sysfunc(getoption(sortsize)); SORTSIZE is: 314572800 81 /* Sort the data*/ 82 proc sort data=test out=test3; 83 by id; 84 run; NOTE: There were 2140000 observations read from the data set WORK.TEST. NOTE: The data set WORK.TEST3 has 2140000 observations and 17 variables. NOTE: PROCEDURE SORT used (Total process time): real time 29.62 seconds user cpu time 5.39 seconds system cpu time 3.79 seconds memory 312110.68k OS Memory 321204.00k Timestamp 06/28/2017 10:44:37 PM Step Count 8 Switch Count 0 And, If the number of SORTSIZE is greater than the number of MEMSIZE, Is it will silently reduced to the number of MEMSIZE?(Just to confirm, from the test, it is true.) ... View more

Hi, guys   Recently, I've met a strange problem. I can't understand that. Hopefully, you guys can explain to me. Here is it:   Let's see an basic result: /* 2 variables and 1 observation in table1 */ data table1; id="001"; num=uniform(1); run; /* 2 variables and 5 observation in table2*/ data table2; input id $ score; cards; 001 80 001 80 001 90 001 60 001 85 ; run; /* Inner Join table1 and table2*/ proc sql; create table result as select a.*,b.score from table1 a inner join table2 b on a.id=b.id; quit; proc print;run;   The Data Set Result is:   Notice the order of variable SCORE, is just same as the original order in table2.   But when the number of variables in table1 increases, magical thing happened. Let's see:   /* 12 variables and 1 observation in table1 */ data table1; id="001"; array num{10} ; do i=1 to 10; num{i}=uniform(i)+i; end; run; /* 2 variables and 5 observation in table2 */ data table2; input id $ score; cards; 001 80 001 80 001 90 001 60 001 85 ; run; /* Inner Join table1 and table2*/ proc sql; create table result as select a.*,b.score from table1 a inner join table2 b on a.id=b.id; quit; proc print;run;   The order of variable SCORE in Data Set RESULT has changed:   So, I'm confused. Why did it change?   Next, I run a test, to see the relation between the number of the variables in data set table1 and the order of varialbe SCORE in data set RESULT. Here is the code:   %macro create_table1(count); %local i; data table1; id="001"; %do i=1 %to &count; num&i=uniform(&i); %end; run; %mend create_table1; %macro test_join; proc sql; drop table differ_all; quit; %local i; %do i=1 %to 20; %create_table1(&i) proc sql; create table result&i as select a.*,b.score from table1 a inner join table2 b on a.id=b.id; quit; data differ&i; merge result(keep=score rename=(score=base)) result&i(keep=score rename=(score=new)) end=last; length score_base score_new $ 20 differ_flag $ 5; retain score_base score_new; score_base=catx(',',score_base,base); score_new=catx(',',score_new,new); if last then do; var_count=&i+1;/*plus the variable ID*/ differ_flag=ifc(score_base=score_new,'true','false'); output; end; keep var_count score_base score_new differ_flag; run; proc append base=differ_all data=differ&i force; run; %end; proc print data=differ_all;run; %mend test_join; data table1; id="001"; num=uniform(1); run; data table2; input id $ score; cards; 001 80 001 80 001 90 001 60 001 85 ; run; proc sql; create table result as select a.*,b.score from table1 a inner join table2 b on a.id=b.id; quit; %test_join And the result is:   When the number of variables of data set table1 equals 11, then order of SCORE in join table has changed. I test a few times, it's the same.   So, anyone can tell me the reason? And I can't find anything about that in SAS Help and Documents.     ... View more