Help using Base SAS procedures

How to read just part of a data?

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

How to read just part of a data?

Hi everyone,

I have what is probably a very easy question, but I can't seem to find the solution after searching for a while. I would appreciate any and all suggestions to my problem below:

I have two data sets and want to merge them with the common variable they have, date. In one data set the year includes the year month and date, the other data set includes just the year and month. For example Data Set 1 would have 196801 for January 1968, and Data Set 2 would say 19680131 for January 31, 1968.

What I need to do is "chop off" the dates in the second data set, so that it only shows 196801 instead of 19680131.

I want to do this because I want SAS to know that these two data sets have a common variable, so that I can merge them into one.

Thanks in advance!


Accepted Solutions
Solution
‎10-17-2014 02:10 PM
Super User
Posts: 10,500

Re: How to read just part of a data?

Are these "date" values numeric or character?

If character you could substring the longer: newdate=substr(olddate,1,6)

if numeric: newdate = int(olddate/100);

Name (and or rename) the "Newdate" variable to match that in the data set with the shorter values or use same variable name in both positions to modify the current value

An I placed date in quotes because SAS has special treatment of numeric values that are created to be used as dates internally which neither of your values look like.

View solution in original post


All Replies
Solution
‎10-17-2014 02:10 PM
Super User
Posts: 10,500

Re: How to read just part of a data?

Are these "date" values numeric or character?

If character you could substring the longer: newdate=substr(olddate,1,6)

if numeric: newdate = int(olddate/100);

Name (and or rename) the "Newdate" variable to match that in the data set with the shorter values or use same variable name in both positions to modify the current value

An I placed date in quotes because SAS has special treatment of numeric values that are created to be used as dates internally which neither of your values look like.

Contributor
Posts: 65

Re: How to read just part of a data?

Hello again,

I have one more related question. I have a date variable in date format. But I want to change it to weekly dates. For example I have my data in YYYY-MM-DD format and I want to say if the date is in the first quarter of a month then change the date into YYYY-MM-W, which W is the number of week from 1 to 4. I tried the following code, but it didn't work.

if DD(datesurveyed)<=7 then weeklydate=(YYYY-MM-01);

Super User
Posts: 10,500

Re: How to read just part of a data?

Reasons to use the SAS date values are the numbers of functions and formats available. The function DAY will give you the day of the month if needed. WEEK will return week of the year.

But there may be a few things to consider with your definition of "week". Except for February most months will have a fractional week. What "week" do the 29th, 30th and 31st days of the month belong to?

Something that may be similar to what you want and doesn't have issues with fractional months are to use one of the WEEK formats, WEEKU, WEEKV and WEEKW depending on which day of the week you want to start for the week and how to treat the days on the ends of the year. Please look up the online help to see which may fit better.

Also there are a number of incrementing functions such and INTNX that are used to get other dates using rules that may apply.

It is also possible to create a custom Format to display date and time information using special directives.

How exactly do you intend to use this value? There may be simple approaches or more complex depending on usage.

Contributor
Posts: 65

Re: How to read just part of a data?

In the data set I'm working on, they did a survey every week, analyzing the characteristics of different groups. However, the survey is not perform at a specific day. For example, one group on Monday, the other on Thursday and so forth. Now I need to find the variation of these weekly observations, for every week and among different groups. But I can't, because even though the data is weekly but the observations are not necessarily for the same date.

So I think maybe it's better to change the date variable to YYYY-MM-WW to be able to compare the observations.

Super User
Posts: 10,500

Re: How to read just part of a data?

Here is an example of using a format with an existing date variable. The custom format monweek groups data in the statistical procedures by the formatted value and, at least as far as I have seen, keeps the lowest value within the group as the value of the date.

/* create a custom format that uses year and week of year. You do not want to include MONTH as then if the month starts on Wednesday then the grouping for that week will be split in mid week.*/

proc format library=work;
picture monweek
low-high = '%Y-%U'  (datatype=date);
run;

/* some example data*/

data junk;
do date = '01JAN2014'd to '31DEC2014'd;
   i+1;
   output;
end;
format date monweek10.;
run;

/* sum the value of I for each week */

proc summary data=junk nway;
   class date;
   format date monweek10.;
   var i ;
   output out=test sum=;
run;

/* print, using a different date format to show a more acceptable calendar

proc print data=test;
   var date i;
   format date mmddyy10.;
run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 298 views
  • 8 likes
  • 2 in conversation