DATA Step, Macro, Functions and more

Import Data from Statistic Sweden, SCB, API to SAS. A solution. Will work for all PX-WEB solutions.

Reply
Contributor
Posts: 44

Import Data from Statistic Sweden, SCB, API to SAS. A solution. Will work for all PX-WEB solutions.

Finaly, I got data from SCB to SAS without any manual labor. Here is my solution.

First, I create a SAS-tabel with the json-question. Here is the code, and everything is needed.

The only problem i have is the dlm="@" Is there a method to import the entiry row I am glad to know.

filename qjson "D:\Data\SAS\SCB_DATA\Question\AM0401UZ.json";
run;

data qscbapi.Q_AM0401UZ;
	length json $ 80;
	format json $char80.;
	infile qjson dlm="@" ;
	input json;
run;

The main program is think to to regular updates, therefore I have to change the dates, 2015K1. I have a variable in the database which records the last date. Using it an construct a macro for the next date, next_k. Therefore a lots of rows before I got to the import.

filename fraga temp recfm=v lrecl=32000;
filename scb_resp temp recfm=v lrecl=32000;
%let url=http://api.scb.se/OV0104/v1/doris/sv/ssd/START/AM/AM0401/AM0401N/NAKUBefolkningLK;

/* Genererar en makrovariabel som är ett kvartal senare än sista inlästa */
data _NULL_;
	set SCB.aku_lan_last_k;
	length next_k $ 6;
	if substr(kvartal,6) = '4' then do;
		next_k=(put(input(substr(kvartal,1,4),4.)+1,4.))||'K1';
	end;
	else do;
		next_k=substr(kvartal,1,5)||(put(input(substr(kvartal,6),1.)+1,1.));
	end;
	call symputx('next_k', next_k);
run;
/* Modifierar json-frågan med kvartalet från makrotvariablen ovan */
data aktuellfraga;
	set QSCBAPI.Q_AM0401UZ;
	if json='"2015K1"' then json='"'||"&next_k"||'"';
run;
/* Gör en json frågefil. Sparas till filnamn:fraga */
data _null_;
	file fraga;
	set work.aktuellfraga;
	put json;
run;
/* Frågar SCB om data och lagrar svaret som en csv-fil i filnamn:scb_resp. */
proc http 
	in=fraga
	out=scb_resp
	url="&url"
	method="post"
    ct="application/json
	headerout=hdout;
	charset=Windows-1252";
run;
/* Läser in SCB:s svarsfil med data till en sas-tabell. (Använde EG:s inlästning och tog koden. OBS: Använd alternativet generell- */
DATA WORK.IN_AM0401UZ;
    LENGTH
        region           $ 23
        'arbetskraftstillhörighet'n $ 18
        'kön'n           $ 7
        '1000-tal 2015K1'n   8 ;
    FORMAT
        region           $CHAR23.
        'arbetskraftstillhörighet'n $CHAR18.
        'kön'n           $CHAR7.
        '1000-tal 2015K1'n NLNUM11.1 ;
    INFORMAT
        region           $CHAR23.
        'arbetskraftstillhörighet'n $CHAR18.
        'kön'n           $CHAR7.
        '1000-tal 2015K1'n BEST6. ;
    INFILE scb_resp
        LRECL=32767
        FIRSTOBS=2
        ENCODING="WLATIN1"
        DLM='2c'x
        MISSOVER
        DSD ;
    INPUT
        region           : $CHAR23.
        'arbetskraftstillhörighet'n : $CHAR18.
        'kön'n           : $CHAR7.
        '1000-tal 2015K1'n : ?? COMMA6. ;
RUN;
/* rensar upp bland tabeller */
proc datasets lib=work nolist ;
	delete aktuellfraga;
run;

The fileimport is a litle cheat. I did it in EG and steal the code.

A am glad for Statistic Finlands example code for SAS. Without it I do not manage to fix it.

Ask a Question
Discussion stats
  • 0 replies
  • 210 views
  • 1 like
  • 1 in conversation