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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: