topic Re: Add variables, assign values & sort in SAS Programming

Add variables, assign values & sort

dhir — Tue, 12 Sep 2017 12:16:59 GMT

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.

Re: Add variables, assign values & sort

Kurt_Bremser — Tue, 12 Sep 2017 12:26:53 GMT

- 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-data-AKA-generate/ta-p/258712 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.

Re: Add variables, assign values & sort

Reeza — Tue, 12 Sep 2017 15:04:25 GMT

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

Re: Add variables, assign values & sort

dhir — Fri, 15 Sep 2017 10:57:57 GMT

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

Re: Add variables, assign values & sort

Kurt_Bremser — Fri, 15 Sep 2017 11:29:35 GMT

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;

Re: Add variables, assign values & sort

dhir — Sat, 16 Sep 2017 10:42:52 GMT

The code work for me.

Thank you KurtBremser.