2011年2月20日日曜日

SQLプロシジャを利用したSASデータセットの存在チェック


Awaiting servers
Creative Commons License photo credit: bugeaters

売上データなどを利用して分析をする場合、複数のデータテーブルを同時に読み込んで処理を実行することがあるかと思います。

例えば・・・

 ・売上データを過去10年分読み込み売上の推移を求める。
 ・直近1年分の売上データ、予算目標データを読み込んで売上目標に満たない店舗を求める。

などなど様々な要件が考えられます。

その様々な要件に応じて対象となるデータを読み込むように処理を作る必要があるわけです。
プログラムを組むという観点では、処理を実行する前に対象となるデータテーブル(SASの場合だとSASデータセット)が存在している事を確認した上で処理を実行するほうが望ましいです。

SASデータセットの存在確認のプログラム例



%SYSFUNC(EXIST(データセット名)) ;



しかし、上記の例で上げたように過去10年分のデータを読み込むなどの場合は、存在確認を10年分記述するのは大変かと思います。
データ件数が膨大だと予想される売上データなどは、月毎にデータを分けて保存していると考えられます。
10年分なので、120ヶ月分の存在確認を記述することになります。
正直大変なので、そのままの方法ではやりたくありません。

WORKの下に以下のようにデータがあるとします。



以下のようなプログラムを使用して、データの確認を行うことが出来ます。


proc sql;
 select
  min(substr(memname,8,6)) as MIN,
  max(substr(memname,8,6)) as MAX,
  count(*) as COUNT_TBL
 from
  dictionary.tables
 where
  libname = "ライブラリ名" and
  substr(memname,1,7) = "データセット名称(一部)"
 ;
quit;


※上記のデータセットの存在を確認する場合は、ライブラリ名に"WORK"、データセット名称には"URIAGE_"を入れます。

実行結果例






DICTIONARY.TABLES にはlibnameで追加された先のSASデータセット(ビュー)、workで作成されたSASデータセット(ビュー)の情報が格納されています。
データセットの作成、または削除された時に意識しなくても更新がかかります。
このテーブルには どのライブラリに存在するかを示す"libname"、とSASデータセット名称を示す"memname"があります。
それを利用してデータセットの確認をしてしまうという方法です。

上記のプログラムはWORKライブラリにある、データセット名称の頭7文字が"URIAGE_"で始まるデータセットの件数を求めるプログラムです。

 ・件数が処理対象になる年月の数と同じになること。
 ・SQLで返される結果の最大の年月が、処理対象の最大年月と同じになることを確認する。
 ・SQLで返される結果の最小の年月が、処理対象の最小年月と同じになることを確認する。

このことにより論理的にデータのチェックを行うことが出来ます。


ただし、この方法には弱点として、読み込む対象のSASデータセット名称が一定の規則で作成されていることが条件になります。
何らかの方法で対象のデータセット群だけを特定する方法が明確である必要があります。

例) 売上データの場合、URIAGE_[年月]という名称の13文字のデータセットで作成する。

などの場合は頭文字7文字分が『URIAGE_』であれば売上のデータとして特定できるため使用ができますが、仮にURIAGE_YOSANというデータセットが存在した場合は正しい件数が取得できなくなる恐れがありますので、抽出条件を変えて特定できるようにするなど工夫が必要です。

投稿者:NI原


[google]

2011年2月1日火曜日

ハッシュオブジェクトによるテーブル結合



以前の記事「SASのハッシュオブジェクトを試してみた」でハッシュオブジェクトを取り上げましたが、今回はそれを使ったテーブル結合について説明していきます。


売り上げ情報が入ったSALESというSASデータセットと、


製品の情報が入ったPRODUCTというSASデータセットを使います。


結合キーは、PRODUCT_CODE(製品コード)です。


まずは、SALES全件を出力対象にしてそれとマッチするPRODUCT情報を付与したデータを作成してみます。

MERGEステートメントでは、

proc sort data=SALES out=SALES_SORT ;
 by PRODUCT_CODE ;
run ;
proc sort data=PRODUCT out=PRODUCT_SORT ;
 by PRODUCT_CODE ;
run ;
data DATA1 ;
 merge SALES_SORT(in=in1) PRODUCT_SORT ;
 by PRODUCT_CODE ;
 if in1 ;
run ;


のように書きます。



ハッシュオブジェクトを使うと以下のように書くことができます。

data DATA1(drop=rc) ;
 if _N_=1 then do ;
  declare hash hash_in2() ;
  rc = hash_in2.DefineKey("PRODUCT_CODE") ;
  rc = hash_in2.DefineData("PRODUCT_NAME","PRODUCT_PRICE") ;
  rc = hash_in2.DefineDone() ;
 end ;
 do until(last_in2) ;
  set PRODUCT end=last_in2 ;
  rc = hash_in2.add() ;
 end ;
 do until(last_in1) ;
  set SALES end=last_in1 ;
  call missing(PRODUCT_NAME,PRODUCT_PRICE) ;
  rc = hash_in2.find() ;
  output ;
 end ;
run ;


順番に説明していきます。

3~6行目がハッシュオブジェクトを定義している箇所です。

declare hash hash_in2() ;

DECLAREステートメントでハッシュオブジェクト名を指定します。
任意のSAS名でなければなりません。(ここではhash_in2)

rc = hash_in2.DefineKey("PRODUCT_CODE") ;

DefineKeyメソッドでルックアップキーを指定します。
MERGEステートメントでの結合キーと同意です。
""で括る必要があります。

rc = hash_in2.DefineData("PRODUCT_NAME","PRODUCT_PRICE") ;

DefineDataメソッドでハッシュオブジェクトに格納するデータ(変数名)を指定します。
""で括る必要があります。
ルックアップキーだけを使用する場合は指定しなくても構いません。

rc = hash_in2.DefineDone() ;

DefineDoneメソッドでハッシュオブジェクトの定義が済んだことを告げます。


ちなみに、このハッシュオブジェクトを定義している箇所が、
if _N_=1 then do ;
end ;
で括られているのは、ハッシュオブジェクトはDATAステップ内で一度だけ定義されれば良いからです。
(DATAステップ内のループ処理で毎回定義しても結果は変わりません)


ここまでの記述で、DATAステップ内でhash_in2というオブジェクトにアクセスすることができるようになります。




次にオブジェクトに値を格納します。

SETステートメントでSASデータセット名を指定し、ADDメソッドで値を格納します。
DO UNTILステートメントとSETステートメントのENDオプションを使い、1オブザベーションずつ処理します。

 do until(last_in2) ;
  set PRODUCT end=last_in2 ;
  rc = hash_in2.add() ;
 end ;

ADDメソッドで値が追加されるデータは、DefineDataメソッドで指定したPRODUCT_NAMEとPRODUCT_PRICEです。

ここまでの記述で、hash_in2というオブジェクトに値が格納されます。



最後にSASデータセットSALESを読み込みながらハッシュオブジェクトにアクセスして値を取得しアウトプットします。
ADDメソッドのときと同様に、DO UNTILステートメントとSETステートメントのENDオプションを使います。

 do until(last_in1) ;
  set SALES end=last_in1 ;
  call missing(PRODUCT_NAME,PRODUCT_PRICE) ;
  rc = hash_in2.find() ;
  output ;
 end ;


FINDメソッドでハッシュオブジェクトから値を取得します。
最後にアウトプットです。

call missing(PRODUCT_NAME,PRODUCT_PRICE) ;

については後ほど説明します。



結果は以下のとおりです。


PRODUCT_CODEに対応するPRODUCT_NAMEとPRODUCT_PRICEがSASデータセットSALESに付与されました。
SASデータセットPRODUCTにないPRODUCT_CODEについてはPRODUCT_NAMEとPRODUCT_PRICEが共に欠損値です。
(変数の並び順を変えたい場合は、別途FORMATステートメント等で調整します)


最後に、
call missing(PRODUCT_NAME,PRODUCT_PRICE) ;

について説明します。

上記ステートメントを入れないで実行した結果を見れば明らかです。



PRODUCT_CODE="0009"のオブザベーションに、PRODUCT_CODE="0005"のPRODUCT_NAMEとPRODUCT_PRICEが入ってしまいます。
これは、FINDメソッドで新しい値をオブジェクトから取得するまでは内部的に前の値を保持するためです。

試しに、下記のようなプログラムを実行してみると、

data DATA1_NG2 ;
 if _N_=1 then do ;
  declare hash hash_in2() ;
  rc = hash_in2.DefineKey("PRODUCT_CODE") ;
  rc = hash_in2.DefineData("PRODUCT_NAME","PRODUCT_PRICE") ;
  rc = hash_in2.DefineDone() ;
 end ;
 do until(last_in2) ;
  set PRODUCT end=last_in2 ;
  rc = hash_in2.add() ;
 end ;
 do until(last_in1) ;
  set SALES end=last_in1 ;
  * PRODUCT_CODEが"0009"のときだけログに内部的な値を出力 ;
  if PRODUCT_CODE="0009" then put "*** FINDメソッド直前の " PRODUCT_NAME= PRODUCT_PRICE= ;
  rc = hash_in2.find() ;
  output ;
 end ;
run ;


ログには、
*** FINDメソッド直前の product_name=製品0005 product_price=5700
のように出力されます。

この状態で次のFINDメソッドで新しい値が取得できずにアウトプットされるので、上記のようなデータになってしまいます。


よって、このようなやり方でテーブル結合させる場合には、FINDメソッドの直前でDefineDataメソッドで定義した変数を初期化させる必要があります。


以上、今回はもっとも基本的なテーブル結合について説明しました。

次回以降、もう少し複雑なテーブル結合や、ハッシュオブジェクトでのテーブル結合のメリット等を取り上げていく予定です。


投稿者:@Massu_NI


[google]

ツイート数からみる"バーチャルYouTuber"ブーム

今や YouTuber の話題の半分を占めるほどのクチコミ数に 当社が提供するソーシャルビッグデータ検索ツールの「 beInsight (ビーインサイト)」を使って、話題の「バーチャル YouTuber 」について調べてみました。 「バーチャル YouTuber...