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.
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;
- 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.
@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.
Input dataset:
Date | Time | Price | Volume | AskPrice | BidPrice | Bidsize | Asksize |
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 |
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:
Id | Date | Time | Price | Volume | AskPrice | BidPrice | Bidsize | Asksize | TQ |
1 | 19890103 | 9:31:48 | 53.5 | 100 | . | . | . | . | T |
2 | 19890103 | 9:31:54 | 53.25 | 100 | . | . | . | . | T |
3 | 19890103 | 9:31:54 | . | . | 81.75 | 81.25 | -99 | -99 | Q |
4 | 19890103 | 9:35:30 | 53.25 | 1000 | . | . | . | . | T |
5 | 19891031 | 15:54:35 | . | . | 81.75 | 81.5 | -99 | -99 | Q |
6 | 19891031 | 15:55:40 | . | . | 81.75 | 81.25 | -99 | -99 | Q |
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;
The code work for me.
Thank you KurtBremser.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.