hhchenfx Tracker

Re: Help with Do Loop return incorrect result

hhchenfx — Wed, 11 Jun 2025 19:07:28 GMT

Hi Tom,

Thanks for checking.

I found the issue.

HHC

	if gap<60 then do;
			if J_low<=low+add_level_3 then do;
			 	entry_time=J_clock_time;
				entry_price=ceil(low * 100) / 100+ add_level_3;
				bigbar=clock_time;
				out=1; leave;
				end;end;
	else 
	if gap<120 then do;
			if J_low<=low+add_level_4 then do;
			 	entry_time=J_clock_time;
				entry_price=ceil(low * 100) / 100+ add_level_4;
				bigbar=clock_time;
				out=1; leave;
				end;end;
	else

Help with Do Loop return incorrect result

hhchenfx — Wed, 11 Jun 2025 18:17:18 GMT

Hi Everyone,

I have an issue with the result produced by the below loop code.

Basically, when body_50 =1 bar[i], then look forward when condition: new row's low[j] < low[i] + add_level_1[i] then mark:

1 - entry_time = time[j]

2 - entry_price =ceil(low[i] * 100) / 100+ add_level_1[i]

As shown in the picture attached, on the row 9:27, body_1 =1 with low[i] + add_level_1[i] = 231.13 and SAS starts look forward.

BUT it pick row 9:30 where low is 131.50 which is bigger than 231.13. It means the condition is Not met and somehow SAS still pick that row.

Any help is very much appreciated.

Thanks,

HHC


proc sort data=ss; by tic date clock_time;run;

data ss1; set ss nobs=nobs;
drop i j_: out ;
format entry_time bigbar time9.;
i+1;
out=0;
by tic;
if  body_50=1 then do; 
	do j=i+1 to nobs until (out=1);
	set ss (keep=tic date clock_time low high rename = (tic=J_tic date=J_date clock_time=J_clock_time low=J_low high=J_high) ) point=j;

			low_plus_add_level_1=low+add_level_1 ;
			gap=(J_clock_time - clock_time) / 60;

	if date^=J_date or tic^=J_tic or J_clock_time>'15:30't then do; out=1; leave; end;
	else
	if gap<20 and J_low<=low+add_level_1 then do;
		 	entry_time=J_clock_time;
			entry_price=ceil(low * 100) / 100+ add_level_1;
			bigbar=clock_time;
			out=1; leave;
			end;
	else 
	if gap<60 and J_low<=low+add_level_3 then do;
		 	entry_time=J_clock_time;
			entry_price=ceil(low * 100) / 100+ add_level_3;
			bigbar=clock_time;
			out=1; leave;
			end;
	else 
	if gap<120 and J_low<=low+add_level_4 then do;
		 	entry_time=J_clock_time;
			entry_price=ceil(low * 100) / 100+ add_level_4;
			bigbar=clock_time;
			out=1; leave;
			end;
	else 
	if gap<180 and J_low<=low+add_level_5 then do;
		 	entry_time=J_clock_time;
			entry_price=ceil(low * 100) / 100+ add_level_5;
			bigbar=clock_time;
			out=1; leave;
			end;
	else 
	if gap>=180 and J_low<= low +add_level_6 then do;
			entry_time=J_clock_time;
			entry_price=ceil(low * 100) / 100+ add_level_6;
			bigbar=clock_time;
			out=1; leave;
			end;
end;
end;
run;

Re: Running time issue

hhchenfx — Sat, 24 May 2025 15:01:56 GMT

Thank you for your response.

My ssd is Samsung - 990 PRO 2TB Internal SSD PCle Gen 4x4 NVMe.

I really dont know what is going on.

Any Mem setting is SAS can alleviate the issue?

Thanks

Running time issue

hhchenfx — Sat, 24 May 2025 04:12:35 GMT

Hi Everyone,

My data is about 5GB with 20 columns and I run this below code.

At first, I dont create NEW file and simply overwrite using: data return_cal_new2; set return_cal nobs=nobs;

It take me 30+ second and in Task Manager, I see my SSD usage is 100% (My SSD has 500+GB free). It is so weird.

OK, I change the name to return_cal_NEW (as in the code below) and run it for the first time. Now it take only 3.4 second using about with about 70% SSD. Great!

Look like over-writing is an issue.

Without deleting the NEW file, I rerun the code 2nd time, and it take me again, 3.3 second with about 70% SSD

Without deleting the NEW file, I rerun the code 3rd, and the weird behavior shows up again, 50 second and 100% SSD

I really curious what is going on here. Of course, I try several time and the pattern is similar.

Is this because of my PC (spec: intel 13700 64GB DDR5) or because of SAS (9.4)? Should I reinstall SAS?

Any explanation is very much appreciated.

HHC

data return_cal2; set return_cal;
if entry_time^=. then do;
	if entry_time^=. and clock_time<='9:40't and (entry_time-clock_time)/60<=10 then  fail=1;
	else 
	if entry_time^=. and clock_time>'9:40't and (entry_time-clock_time)/60<=5 then  fail=1;
	else
	fail=.;
end;
run;

Transpose multiple columns Long to Wide

hhchenfx — Tue, 12 Mar 2024 03:30:38 GMT

Hi Everyone,

Is there any better way than the one below?

If I have to transpose 20 columns, I have to run that SQL 19 times. Of course, I can put that into Macro, but I bet there should be a better method.

Thank you,

HHC

data long2; 
  input famid year faminc spend ; 
cards; 
1 96 40000 38000 
1 97 40500 39000 
1 98 41000 40000 
2 96 45000 42000 
2 97 45400 43000 
2 98 45800 44000 
3 96 75000 70000 
3 97 76000 71000 
3 98 77000 72000 
; 
run ;

proc transpose data=long2 out=widef prefix=faminc;
   by famid;
   id year;
   var faminc;
run;

proc transpose data=long2 out=wides prefix=spend;
   by famid;
   id year;
   var spend;
run;

data wide2;
    merge  widef(drop=_name_) wides(drop=_name_);
    by famid;
run;

proc print data=wide2;
run;

Re: SAS Error Unable to sample external file, no data in first 5 records

hhchenfx — Fri, 08 Mar 2024 22:57:26 GMT

Thanks, Tom.

I fixed the typo.

The error shown when the file doesn't exist 😕

HHC

SAS Error Unable to sample external file, no data in first 5 records

hhchenfx — Fri, 08 Mar 2024 22:56:10 GMT

Hi,

I run a macro to check if a csv file exist before importing it.

When it run the first time, as the csv file is not there yet, it give me a error (instead of move on to Else condition).

That happen several times.

Do you guys know what is going on and how to fix?

Many thanks,

HHC

%macro import_csv(file_path);
    %if %sysfunc(fileexist(&file_path.)) %then %do;
		proc import datafile="&file_path" out = runaway_1 replace; run;	%end;
    %else %do;
		%put no data yet;    %end;
%mend;
%import_csv(E:\Run_Away.csv);

Get the highest value within a window

hhchenfx — Wed, 06 Mar 2024 05:34:57 GMT

Hi Everyone,

So I have a data with ID, date (not continuous) and value.

For each row, I create a look back, which is 4 date before.

I want to find the max value within that 4 date (excluding current date)

My code below gives me the result but I need to run a sort nodupkey.

Can you please help me fix it?

Thanks,

HHC


data have;
input id date value;
datalines;
1 1 3
1 4 7
1 15 3
1 25 20
1 26 8
1 27 15
1 28 5
1 29 5
1 30 5
1 31 6
;
run;

data have; set have;
lookback_date=date - 4;
run;

proc sql;
create table want as select a.*, max(b.value) as max from have as a left join have as b 
on a.id=b.id and a.lookback_date<=b.date and a.date>b.date
group by a.id, a.date;
quit;

proc sort data =want out=want nodupkey; by id date; run;

Issue with WORK.PROFILE will be opened instead

hhchenfx — Tue, 27 Feb 2024 13:08:39 GMT

Hi,

I got new PC and I install SAS9.4 in my C: drive (C:\Program Files\SASHome\SASFoundation\9.4).

I copied my old document from my old PC to the new PC in folder E:\backup as below and in this folder, turns out this back up also has "My SAS Files".

Now when run my SAS, it always point to this folder with the below error.

How can I fix it?

Thanks,

HHC

ERROR: Windows error code: 362 in hc_normal_read_uhb for E:\backup\Documents\My SAS Files\9.4\profile.sas7bcat, The cloud file provider
is not running.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead

import specific sheet of an xlsx file into SAS

hhchenfx — Sat, 24 Feb 2024 03:10:21 GMT

Hi Everyone,

I need to import a sheet of an Excel file (xlsx) into SAS using data step (infile rather than import).

Also, 1 column is date in the format Feb-25-2019 and I don't know how to read it correctly.

Can you please help?

Many thanks,

HHC

Macro check if dataset is available

hhchenfx — Tue, 20 Feb 2024 04:57:03 GMT

Hi,

I want to check if dataset Myfile is already in work library or not. If it is not there yet, create that file.

The idea can be seen in the code below.

Thank you,

HHC

%macro check_file_exist;
	%if FILE IS NOT THERE %then %do;
		data Myfile; set _NULL_;
		run;
	%end;
%Mend;

Re: Combine table to multiply weight to value

hhchenfx — Thu, 18 Jan 2024 01:04:19 GMT

Thank you so much, Tom!

HHC

SAS issue with sum

hhchenfx — Thu, 18 Jan 2024 00:34:45 GMT

Hi Everyone,

I run the below code to get weight schedule.

The problem is that when I want to get (sum of element)=1, SAS do not return row value such as factor1=0, factor2=0 and factor3=1.

I am not sure if it is my SAS issue or else, thus I keep the count in each part so you can can check.

Can you please help to review?

Thanks,

HHC

data have;
  do factor1 = 0 to 1 by 0.1;
    do factor2 = 0 to 1 by 0.1;
      do factor3 = 0 to 1 by 0.1;
		output;
      end;
    end;
  end;
run;
/*The data set WORK.HAVE has 1331 observations and 3 variables*/

data weight_schedule; set have;
if factor1 + factor2 + factor3 = 1;
run;
/*The data set WORK.WEIGHT_SCHEDULE has 53 observations*/

/********this return CORRECT count*******/

data CORRECT_COUNT; set have;
if 0.99<factor1 + factor2 + factor3<1.01;
run;
/* The data set WORK.W has 66 observations*/

Combine table to multiply weight to value

hhchenfx — Wed, 17 Jan 2024 20:50:49 GMT

Hi Everyone,

I have 2 datasets:
1- specification contain 4 variable name (v1-v4) and it associated weight (weight1-weight4)
2- timeseries contain value of each variable name and its value overtime.

What I want is to take 1 row in specification file and multiply with all rows in timeseries.
So for the first row in specification, I will create a new dataset from timeseries where I do:

if name ='a' then value=value*0; else
if name = 'b' then value=value*0.1; else
if name = 'c' then value=value*0.1; else
if name = 'd' then value=value*0.8;

and keep only those with name a b c d.

(0, 0.1, 0.1,0.8 is the weight taken from specification file)

I will perform further analysis with this new dataset.

(This explanation of 1 row is intuition.
However, if you can help to create a file that contain all multiplication is great)

Can you please help?
Many thanks,
HHC


data specification;
input var1 $ var2 $ var3 $ var4 $ weight1 weight2 weight3 weight4 ;
datalines;
 a b c d 0 0.1 0.1 0.8
 a b v c 0.7 0.1 0.1 0.1
 ;run;

 data timeseries;
 input name $ value;
 datalines;
 a 1
 a 1.2
 a 1.3
 b 2
 b 2.1
 b 2.2
 c 3
 c 3.1
 c 3.2
 c 3.3
 d 4.1
 d 4.2
 d 4.3
 v 5.1
 v 5.2
 v 5.3
;run;

Re: Run code every minute

hhchenfx — Tue, 09 Jan 2024 20:15:30 GMT

Thank you so much for helping!

HHC

Re: Run code every minute

hhchenfx — Tue, 09 Jan 2024 17:34:25 GMT

Hi Yabwon,

Can you make your code run at MM:30?

Thanks,

Re: Run code every minute

hhchenfx — Tue, 09 Jan 2024 16:42:36 GMT

I am running SAS9.4.

Thanks,

HHC

Run code every minute

hhchenfx — Tue, 09 Jan 2024 20:17:15 GMT

Hi Everyone,

This code below runs every minutes at MM:50.

One problem is that the code run inside take about 30 seconds. Thus, when it starts running at 10:50, it will be done at 11:20 and the next run will be 1 minute later, which is 12:50.

Thus, the code will not be run at 11:50.

Can you please help to fix the code to make it runs every MM:50?

Thank you,

HHC



%MACRO RUN_each_MINUTE() ;
%DO %WHILE (   %sysevalf(%sysfunc(time())<'10:00:08't,boolean)  ) ;

	/****MY CODE ****/

    %put %sysfunc(wakeup("%sysfunc(putn(%eval(%sysfunc(intnx(minute,"%sysfunc( time(), time )"t,1))+50),time.))"t));

%end ;

%mend ;

%RUN_each_MINUTE();run;

/*Keep sas sleep here and wake up at next minute at MM:55 to start the loop*/
%put %sysfunc(wakeup("%sysfunc(putn(%eval(%sysfunc(intnx(minute,"%sysfunc( time(), time )"t,1))+40),time.))"t));

%MACRO RUN_each_MINUTE(end) ;
%local t;
%put START;

%DO %WHILE (   %sysevalf(%sysfunc(time())<&end.,boolean)  ) ;
  %let t = %sysfunc(datetime());
  		data _null_;call symput ('start_time',put (time(),time.));run;

  		data a; set sashelp.stocks;run;

		data _null_;call symput ('timenow',put (time(),time.));call symput ('datenow',put (date(),date9.));run;
		%put start time;%put &start_time;
		%put END of RUN;%put &datenow;%put &timenow;

		  /* sleep for remaining time after execution */
		  %let t = %sysevalf(60 - (%sysfunc(datetime()) - &t.));
		  %if %sysevalf(&t. > 0) %then %put %sysfunc(sleep(&t.,1));

%end ;

%put END;
%mend ;
%RUN_each_MINUTE('17:25:08't)

Re: Check if current time less than a value

hhchenfx — Tue, 02 Jan 2024 22:47:11 GMT

Thank you so much for helping!

HHC

Check if current time less than a value

hhchenfx — Tue, 02 Jan 2024 21:09:31 GMT

Hi,

I want to compare current time with '4:55't as below but my code is not working.

Can you please help to fix?

Thanks,

HHC

%MACRO _BREAK() ;
%let current_time = time(); /* Get the current time */

%IF &current_time < '4:55't %then %do;
	%put "print before ";
	%END;
%ELSE %do;
	%put "print after";
	%END;

%Mend;

%_BREAK();