topic Re: Convert Date and sort out data in SAS Programming

Convert Date and sort out data

moumes — Sun, 12 Feb 2017 01:32:14 GMT

Hi i am a begginer on SAS and i need some help:

My first problem is converting the date, i have the following format : 20020331 so YYYYMMDD and i would like to have the year and the quarter so this format 2002Q1 (YYYYQQ ).

My second problem is about merging two databases based on mutual information included on both of them: my first database is a large database containing the date, the ID number and other variables for example:

Global Company Key	Fiscal Data Year and Quarter	Current Assets - Total	Assets - Total	Cash and Short-Term Investments	Long-Term Debt - Total	Current Liabilities - Total	Liabilities - Total	Net Income (Loss)
1034	2002Q1	639,409	2367,051	9,689	880,936	368,339	1373,972	-31,94
1034	2002Q2	695,609	2448,679	12,884	899,789	381,368	1384,204	10,159
1034	2002Q3	700,596	2416,691	41,2	907,505	345,285	1357,847	-5,946

And my second database contains the date and the ID number for example:

RankDate	RankdateQ	targetGkey
20091102	2009Q4	1034

I would like to extract the variables included in the first database based on specific date included in the second one.

It would be really helpfull if someone can help, i am struggling with this for quiet a while.

Thank you in advance.

Re: Convert Date and sort out data

Shmuel — Sun, 12 Feb 2017 05:56:34 GMT

1) Use proc contents to post your variable names on each database with their type and format

2) In your sample - first database have year 2002 while the second is 2009.
In such case there is no matching and you'll get empty result.

3) Please post your sample data in a way we don't need to retype the data

in order to run a test

Re: Convert Date and sort out data

moumes — Sun, 12 Feb 2017 15:44:41 GMT

Thank you for your reply, those are the databases i am using :

as you see in the first database i have the Global Company Key which is a variable that identifies the company and values for other variables for each quarter from 2002 to 2013.

In the second database i have the Global Company Key and a specific date in this format YYYYMMDD, i need to convert this format to quarters and then extract the value of the variables included in the first database based on the dated specified in the second one.

Thank you a lot for your hep.

Re: Convert Date and sort out data

Shmuel — Sun, 12 Feb 2017 21:03:10 GMT

For your first question - converting dates - run next test code:

data test;
    dq = '2016q3';                     /* variable given in char type */
    dt = input('20160728',yymmdd8.);   /* converting given char date into sas numeric date */
    format dt yyq6.;                   /* format defines how to display the date, in this case: yyyyQn */

    /* next line compares the quarter to the date - are they the same quarter */
    if upcase(dq) = strip(put(dt,yyq6.)) then put 'OK'; else put 'NOK';
run;

Try to have your own code for merging. Post your code and what issues you face.

You better create a small sample for test, to save time and make it simple.

Re: Convert Date and sort out data

art297 — Sun, 12 Feb 2017 22:54:36 GMT

I don't know how you imported your data. I used proc import, using the dbms=xlsx option.

Using that method, the company IDs were character in one file, numeric in the other.

Similarly, the dates in one file were numbers rather than dates and, in the other, were quarters represented as a character variable.

Here is the code I ran to solve your problem of merging the two files:

proc import datafile="/folders/myfolders/first_database.xlsx" 
  out=first_database replace dbms=xlsx;
  getnames=yes;
  mixed=yes;
  sheet='Sheet1';
  usedate=yes;
  scantime=yes;
run;

proc import datafile="/folders/myfolders/second database.xlsx" 
  out=second_database replace dbms=xlsx;
  getnames=yes;
  mixed=yes;
  sheet='Sheet1';
  usedate=yes;
  scantime=yes;
run;

data first_database (drop=_:);
  set first_database (rename=(
  fiscal_data_year_and_quarter=
   _fiscal_data_year_and_quarter));
  format fiscal_data_year_date date9.;
  fiscal_data_year_date=
   mdy(substr(_fiscal_data_year_and_quarter,6,1)*3,1,
   substr(_fiscal_data_year_and_quarter,1,4));
  fiscal_data_year_and_quarter=put(fiscal_data_year_date,yyq6.);
run;

proc sort data=first_database;
  by global_company_key;
run;

options datestyle=YMD;
data second_database (drop=_:);
  set second_database (rename=(
  announcedate=
   _announcedate
   global_company_key=_global_company_key));
  format announcedate date9.;
  announcedate=
   input(strip(_announcedate),anydtdte8.);
  global_company_key=input(_global_company_key,8.);
run;

proc sort data=second_database;
  by global_company_key;
run;

data want;
  merge first_database second_database;
  by global_company_key;
run;

HTH,

Art, CEO, AnalystFinder.com