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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

- 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.

Reeza
Super User

@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.

dhir
Obsidian | Level 7

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

 

 

Kurt_Bremser
Super User

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;

dhir
Obsidian | Level 7

The code work for me. 

 

Thank you KurtBremser. 

 

 

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
  • 980 views
  • 1 like
  • 3 in conversation