I have the following dataset and what I want to do is listed below:
data have;
infile cards truncover expandtabs;
input MC $ ET $ Date :date9. Time :time. PMC $ PXMC $ Site $ Dia MV SF;
format date date8. Time HHMM.;
cards;
US000409 Meas 12Nov2016 17:26 7101 Et1 1 . 2780462.00000 1
US000409 Meas 12Nov2016 17:33 7101 Et1 1 861.26 2780462.00000 1
US000409 Lath 12Nov2016 17:33 7102 Et1 1 861.6 2780462.00000 1
US01036 Meas 12Nov2016 17:26 7101 Et1 2 . 522860.00000 1
US01036 Lath 13Nov2016 17:33 7202 Et1 2 866.68 522860.00000 1
US01036 Meas 13Nov2016 17:33 7101 Et1 2 867.36 522860.00000 1
US02044 Meas 13Nov2016 17:26 7202 Et1 1 . 569298.00000 1
US02044 Lath 13Nov2016 17:33 7202 Et1 1 865.32 569298.00000 1
US02044 Meas 14Nov2016 17:33 7202 Et1 2 865.68 569298.00000 1
US318 Lath 14Nov2016 17:26 7101 Et2 2 . 2630856.00000 1
US318 Meas 14Nov2016 17:33 7202 Et2 3 863.26 2630856.00000 1
US318 Lath 14Nov2016 17:33 7202 Et2 3 863.94 2630856.00000 1
US000409 Meas 15Nov2016 21:56 7202 Et2 3 860.98 2780462.00000 1
US000409 Meas 15Nov2016 21:56 7203 Et2 4 861.5 2780462.00000 1
US01036 Lath 16Nov2016 21:56 7101 Et2 4 866.64 522860.00000 1
US01036 Meas 16Nov2016 21:56 7202 Et2 4 867.34 522860.00000 1
US02044 Lath 17Nov2016 21:56 7203 Et2 1 865.3 569298.00000 1
US02044 Meas 17Nov2016 21:56 7204 Et2 3 865.68 569298.00000 1
US318 Lath 17Nov2016 21:56 7204 Et2 2 863.24 2630856.00000 1
;
run;
I want to compuet:
1. Daily Mean of 'Dia' for each 'MC'
2. For each 'PMC', step 1 above
3. For each 'MC', what is the difference between 'MV'
4. For each Year in 'Date', how many conts 'Lath' are there for each 'MC'
Please help me learning SAS. Thanks.
Well, look at the code you have written versus the code I provided. You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed. So behind the scenes you are grouping your data based on the full data. Why did you not use the year() function as I provided above?
Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():
proc sql; create table WANT as select year(DATE) as YR, count(ET) as RESULT from HAVE group by year(DATE); quit;
Well (note, make sure dataset is sorted)
1)
proc means ...;
by mc date;
...
run;
2)
proc means ...;
by pmc date;
...
run;
3)
Not sure what you mean, do you mean difference between largest, and smallest, first/last, sum them all - first?
4)
proc sql;
create table WANT as
select yearpart(DATE) as YR,
count(ET) as RESULT
from HAVE
group by yearpart(DATE);
quit;
The above have many examples in the SAS documentation, and on here.
Do also note, you could do all of the given problems in one datastep, use retain variables, and set your by groups, but maybe best to learn to do each bit before jumping in.
Thank you so much.
in 3). I means MV2 - MV1 i.e. current - lag value (but datewise)
in 2). Daily Mean of 'Dia' for each 'MC' grouped by
I appreciate your advice. I just started learning SAS and miles to go. 🙂
@imanojkumar1 wrote:
Thank you so much.
in 3). I means MV2 - MV1 i.e. current - lag value (but datewise)
If you want to calculate the current record difference from the previous record for the same variable SAS provides a function DIF to do that.
However do you want that different only with the same date (from your datewise comment) or the max difference between two days, the range for a given date (maximum - minimum) or something else? And do you what that comparison to go across ET? Is the data allowed to be sorted so that all of MC are together?
Another fundamental question is also do you expect a data set as the result or a report for people to read? It may help a bit to show what you would expect to see from your given example data.
yes Sorting is possible on all the variable except for SF. Thanks for the advice.
Could you please let me know how DIF can be used here?
I think is it...
proc sql;
create table WANT as
select year(DATE) as YR,
count(ET) as RESULT
from HAVE
group by year(DATE);
quit;
*For timestamp it will be year(datepart(datetimestamp));
proc sql;
create table WANT as
select year(DATE) as YR,
count(ET) as RESULT
from HAVE
group by year(DATE);
quit;
does not work 😞
in the result there are 19 rows and each rows has 2016 and 19 as YR and Result.
2016 19
2016 19
2016 19
2016 19
2016 19
. .
. .
Then,
I tried this:
PROC SQL;
CREATE TABLE HAVE01 AS
SELECT t1.*, t1.Date FORMAT=YEAR4. LABEL="Year" AS Year
FROM HAVE t1;
QUIT;
PROC SQL;
CREATE TABLE WANT AS
SELECT Year, (COUNT(ET)) AS COUNT_of_ET
FROM HAVE01
GROUP BY Year;
QUIT;
And expected somthing like this:
Year Count_of_ET
2015 4
2016 15
But got like this:
Year Count_of_ET
2015 2
2015 1
2015 1
2016 1
2016 2
2016 1
2016 4
2016 2
2016 2
2016 3
Well, look at the code you have written versus the code I provided. You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed. So behind the scenes you are grouping your data based on the full data. Why did you not use the year() function as I provided above?
Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():
proc sql; create table WANT as select year(DATE) as YR, count(ET) as RESULT from HAVE group by year(DATE); quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.