DATA Step, Macro, Functions and more

Add variables, assign values & sort

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Add variables, assign values & sort

Hello, I need to add variables in an existing dataset, assign values to one of them based on condition. Then sort the dataset based date & time (in that order) and add serial no. to the observation. The input and output required are shown in the attached excel. 

 

Tried writing a code using input, assign statement but could not achive the desired output.

 

Thank you in advance. 


Accepted Solutions
Solution
‎09-16-2017 06:40 AM
Super User
Posts: 7,845

Re: Add variables, assign values & sort

Your "have" and "want" data don't match Date- and Time-wise (

19890103 9:35:30

is not present in have, while

19891031 15:49:32

is missing from want.

Otherwise, this code will do what you want:

data have;
infile cards dlm='09'x;
input Date :yymmdd8. Time :time8. Price Volume AskPrice BidPrice Bidsize Asksize;
format Date yymmddn8. Time time8.;
cards;
19890103	9:31:48	53.5	100	.	.	.	.
19890103	9:31:54	53.25	100	.	.	.	.
19890103	9:32:30	53.25	1000	.	.	.	.
19891031	15:49:32	.	.	81.75	81.25	-99	-99
19891031	15:54:35	.	.	81.75	81.5	-99	-99
19891031	15:55:40	.	.	81.75	81.25	-99	-99
;
run;

data want_int;
set have;
if price >= 0 or volume >= 0
then TQ = 'T';
else TQ = 'Q';
run;

proc sort data=want_int;
by date time;
run;

data want;
Id = _n_;
set want_int;
run;

Note what I meant by "present data in a data step". Anybody can create an exact replica of my test data with a simple copy/paste and run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,845

Re: Add variables, assign values & sort

- to post data, use a datastep; use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create a datastep that contains an exact replica of your existing dataset.

- post the code you already have; use the "little running man" icon to open a window that supports posting of code as is (no reformatting by the forum software)

- post an example of the desired output (simple table is sufficient)

- do not use the Excel format for SAS data. Excel spreadsheets cannot convey important information (metadata, attributes) of SAS datasets, and are blocked at many corporate institutions because of security reasons. Posting Excel files automatically reduces the number of possible helpers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,855

Re: Add variables, assign values & sort


dhir wrote:


Tried writing a code using input, assign statement but could not achive the desired output.

 


Post what you've tried, explain your question in detail and include your data and question directly into the forum rather than as an attachment please.

Occasional Contributor
Posts: 17

Re: Add variables, assign values & sort

Input dataset: 

 

DateTimePriceVolumeAskPriceBidPriceBidsizeAsksize
198901039:31:4853.5100....
198901039:31:5453.25100....
198901039:32:3053.251000....
1989103115:49:32..81.7581.25-99-99
1989103115:54:35..81.7581.5-99-99
1989103115:55:40..81.7581.25-99-99

 

Steps required in SAS Code:

 

1 . Add 2 variables TQ (String 1 character) and Id (numeric) at the end and beginning of variables respectively

 

2.  Assign values of TQ to each observation

2 (a)  If Price or volume greater than or equal to zero assign "T"

2 (b) else assign "Q"

 

3. Sort all observations:

3 (a) first by date (smallest to largest)

3 (b) second by time (smallest to largest)

 

4. So that are listed date & time wise

 

5. Then add serial no in the id column (1 to end of file)

 

Output dataset: 

 

IdDateTimePriceVolumeAskPriceBidPriceBidsizeAsksizeTQ
1198901039:31:4853.5100....T
2198901039:31:5453.25100....T
3198901039:31:54..81.7581.25-99-99Q
4198901039:35:3053.251000....T
51989103115:54:35..81.7581.5-99-99Q
61989103115:55:40..81.7581.25-99-99Q

 

 

Solution
‎09-16-2017 06:40 AM
Super User
Posts: 7,845

Re: Add variables, assign values & sort

Your "have" and "want" data don't match Date- and Time-wise (

19890103 9:35:30

is not present in have, while

19891031 15:49:32

is missing from want.

Otherwise, this code will do what you want:

data have;
infile cards dlm='09'x;
input Date :yymmdd8. Time :time8. Price Volume AskPrice BidPrice Bidsize Asksize;
format Date yymmddn8. Time time8.;
cards;
19890103	9:31:48	53.5	100	.	.	.	.
19890103	9:31:54	53.25	100	.	.	.	.
19890103	9:32:30	53.25	1000	.	.	.	.
19891031	15:49:32	.	.	81.75	81.25	-99	-99
19891031	15:54:35	.	.	81.75	81.5	-99	-99
19891031	15:55:40	.	.	81.75	81.25	-99	-99
;
run;

data want_int;
set have;
if price >= 0 or volume >= 0
then TQ = 'T';
else TQ = 'Q';
run;

proc sort data=want_int;
by date time;
run;

data want;
Id = _n_;
set want_int;
run;

Note what I meant by "present data in a data step". Anybody can create an exact replica of my test data with a simple copy/paste and run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: Add variables, assign values & sort

Posted in reply to KurtBremser

The code work for me. 

 

Thank you KurtBremser. 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 114 views
  • 1 like
  • 3 in conversation