BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
UltimateWing
Fluorite | Level 6

Hello, I use SAS 9.4 Foundation.

I have Date, In, Out1, Out2,

and want to create variables HoldOn and Num.

The regular is 

if In=1, then I buy the stock at tomorrow,

and if Out1 or Out2 =1, then I sell the stock I've held immediately,

but that day remain to mark TRUE.

The red textcolors are the symbol I need, others are noise.

 

DateInOut1Out2HoldOnNum
      
2015/7/51..  
2015/7/61..TRUE1
2015/7/71..TRUE1
2015/7/8...TRUE1
2015/7/9.1.TRUE1
2015/7/1011.  
2015/7/11...TRUE2
2015/7/121..TRUE2
2015/7/13...TRUE2
2015/7/14..1TRUE2
2015/7/15.1.  
2015/7/161..  
2015/7/171..TRUE3
2015/7/18..1TRUE3
2015/7/1911.  
2015/7/20.1.TRUE4

 

If I use VBA, I can code like bottom:

 

Dim HoldOn as Boolean, No as Integer
HoldOn=False
NO=0
For i=3 to 20
  If HoldOn=True Then
      Range("E" & i).Value=HoldOn
      Range("F" & i).Value=NO
  End If
  
  If HoldOn=False And In=1 Then
      HoldOn=True
      NO=NO+1
  End If


  If HoldOn=True And (Out1=1 Or Out2=1) Then HoldOn=False
Next i

 

The data is too large to import to excel, so I just only use SAS to process my data.

I tried to code in SAS, used Macro

but it can't change Macro variable "HoldOn" into 1 and 0

how could I modify my SAS code

Thank you!!

 

options symbolgen;
%let HoldOn=1;


%macro qq();

    %if &HoldOn=0 %then %do;
        HO=0;
    %end;

    %if &HoldOn=0 %then %do;
        if In1=1 then do;
            %let HoldOn=1;
        end;
    %end;
    %if &HoldOn=1 %then %do;
        if out1=1 then do;
            %let HoldOn=0;
        end;
    %end;

%mend qq;

data TMP0;
    set P2;

    do i=1 to 600;
        if i=n then do;
            %qq();
        end;
    end;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
UltimateWing
Fluorite | Level 6

https://drive.google.com/drive/folders/0BydLJN40NoEEaUNSWUhPV3BwOTg?usp=sharing

 

I have already completed the data what I need.

I use SAS/IML to code structure language.

The new Hyperlink of google drive has an Original SAS Data and a SAS code.

Thanks for all !!

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, your post is quite confusing.  Start by posting some test data - in the form of a datastep - so that we have something to work on.

Also, maybe clarify your logic somewhat Num is never mentioned for instance.  HoldOn I presume becomes True in the record after IN=1 yes, but why is there rows where none are marked?

 

Do note, you have lag() function which can look at previous results, or you could retain the previous values.

UltimateWing
Fluorite | Level 6

Thanks to RW9

I upload my sasdata and what I want in excel

I just use 150 stocks as the sample

tmp0.sas7bdat is original data

TMP0.xlsm is I want to get in SAS

 

and

 

I clarify what I demand

I will use excel to explain

 

Column J is HoldOn , it is a Boolean Variable

if In1=1 then I use next rawdata Open Price as EnterPrice

so in the sample, row 15 has a signal

and I enter to market at row16.

 

Column K is Serial Number, it is "Num" I used

it is nothing within this question, so you can ignore this column

 

Column L is EnterPrice, I've explained above.

 

Column N is out2, it is the problem why I don't use "retian" in SAS to create HoldOn

if rawdata's LowPrice smaller than EnterPrice*0.9, then I need to stop my loss.

And I need to mark out2=1, just like row 136.

In SAS, if I use Variable "In" and Variable "out1" to control "HoldOn", 

I would hold E1101 from row 135 to row 249, and there would have the same EnterPrice.

Actually, I held E1101 from row 135 and sell them at row 136;

I held another Serial Number Trade from row 218 to row 244.

The two Serial Number Trade have different EnterPrice,

so if I nee to use "retain" to create "HoldOn", I think I don't have any idea to code.

 

Column M is OutPrice, if I use out1 to sell my stock I've held, the OutPrice is ClosePrice;

if I use out2 to sell it, the OutPrice is LowPrice.

 

Column O is Returns, which calculate I loss or gain in one trade.

 

My question is :

Is SAS can create a Boolean variable like VBA which can change inner value at one time running?

Thank you!!

 

P.S. Open the Excel, you can push Alt+F11 to open VBA Editor.

 

tmp0.sas7bdat has 4.9mb, system not let me upload...

I use google drive

https://drive.google.com/drive/folders/0BydLJN40NoEESlgtenZZcXRFbEE?usp=sharing

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, that has just confused the question even further for me.  A simple bit of test data (in the form of a datastep) and example output should suffice, I am not reading pseudo VBA (as this is a SAS forum), and certainly not downloading Office files from Google.  Have a look at other questions posted here if you need an explanation of how to post questions.

UltimateWing
Fluorite | Level 6

https://drive.google.com/drive/folders/0BydLJN40NoEEaUNSWUhPV3BwOTg?usp=sharing

 

I have already completed the data what I need.

I use SAS/IML to code structure language.

The new Hyperlink of google drive has an Original SAS Data and a SAS code.

Thanks for all !!

Tom
Super User Tom
Super User

Don't use macro variables for this project.  Just write the logic you need using normal datat step code.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 932 views
  • 0 likes
  • 3 in conversation